A design question in a parent child relationship

Question

A design question in a parent child relationship

eichnerm

Old Hand

Points: 373
More actions
October 2, 2026 at 1:25 am

#5050909
I am not sure which is the best way to proceed. I have parent child relationship where I have to determine if every vendor completed a task (this is a fictitious example).
I have a task record and typically 13 or fewer vendors who each have to complete that task. My application needs to know when all the vendors have completed the their task.
I see two options:
1. Every time I need to know if all the vendors completed the task I could loop thru the vendors assigned to that task
2. I could add a flag to the Task table that lets me know if all the vendors completed the task. If the flag is True I wouldn't have to loop thru all the vendors anymore.
Option 1 has performance issues. Option 2 feels wrong, but it does save database queries.
Which option is the better option? Is there an option that I haven't thought about?
Thanks

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63709 More actions · Answer 1

Looks like you're missing a table... the "Assignment" table...

CREATE TABLE Assignment(
AssignmentID INT IDENTITY,
TaskID INT NOT NULL,
VendorID INT NOT NULL,
DateCompleted DATE
PRIMARY KEY (TaskID, VendorID)
FOREIGN KEY (TaskID) REFERENCES Task(TaskID),
FOREIGN KEY (VendorID) REFERENCES Vendor(VendorID)
);

Then you can cross join the Task and Vendor tables to return all combinations of (TaskID, VendorID) and then you can just subtract any completed tasks from the Assignment table. or do something like

SELECT  *
FROM Task t
WHERE NOT EXISTS (SELECT 1 FROM Assignment a WHERE a.TaskID = t.TaskID AND t.CompletionDate IS NULL OR t.CompletionDate > GETDATE() );

Do you mean the latest date/time that the final task was completed?

SELECT MAX(t.CompletionDate)
FROM Task t
WHERE NOT EXISTS (SELECT 1 FROM Assignment a WHERE a.TaskID = t.TaskID AND t.CompletionDate IS NULL OR t.CompletionDate > GETDATE() );

This reply was modified 3 weeks ago by pietlinden. Reason: Not sure I know how to speak English

Phil Parkin SSC Guru Points: 247234 More actions · Answer 2

Option 2 looks good to me.

Add an IsFullyCompleted? Bit column to your Task table and check/set its value every time a vendor completes a task (I am making the assumption that the 'complete task' code is under your control).

ScottPletcher SSC Guru Points: 101258 More actions · Answer 3

Option 1 has performance issues.

Not necessarily.

Qs:

Are all Vendors added to the Task when the Task is created, or can Vendors be added later?
Can a Task be "Uncompleted"? That is, can a Task be marked as "Completed", but that get deemed insufficient and the Completion reversed?

If 1=Yes and 2=No, particularly, try this:

Uniquely Cluster the table by ( task_id, vendor_id ). Then checking across a task for all vendors completed is minimal I/O anyway.

For example:

CREATE TABLE dbo.task_assignments (
    task_id int NOT NULL,
    vendor_id int NOT NULL,
    assigned_date datetime NOT NULL /* could perhaps be NULL if you can "maybe assign" a vendor */
        CONSTRAINT task_assignments__DF_assigned_date DEFAULT GETDATE(), 
    is_completed bit NNOT NULL 
        CONSTRAINT task_assignments__DF_is_completed DEFAULT 0,
    completed_date datetime NULL,
    ...
    CONSTRAINT task_assignments__CL UNIQUE CLUSTERED ( task_id, vendor_id )
        WITH ( DATA_COMPRESSION = <your_preference>, FILLFACTOR = <your_preference> )
    )

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

eichnerm Old Hand Points: 373 More actions · Answer 4

Let me answer your questions first. A vendor cannot be added. A task is considered incomplete until all vendors have updated the system to say that they are done.

Your approach is one that I hadn't thought of. I am not as experienced or knowledgeable as you are. I am curious how adding a third table (the assignments table) is any better than

Option 1 - Getting a count of Vendors for the task that haven't updated their job status as complete?

Or Option 2 - How adding a flag to the Task table that indicates whether all the vendors have completed their assigned task?

I was always taught that option 2 was a bad idea for a various reasons.

Thanks for you advice.

eichnerm Old Hand Points: 373 More actions · Answer 5

Yes. Everything is under my control.

Something about option 2 feels wrong. I suppose that comes from an old boss and a database seminar that I went to. They said that it was a bad idea because it was:

a) a bad design option

b) there could be an issue if the flag in the Task table doesn't match the data in the vendors table. In other words, if the task is marked complete, but not all of the vendors have completed their task. To me that should never happen unless there is a programming bug.

Thanks

pietlinden SSC Guru Points: 63709 More actions · Answer 6

I was asking about a "VendorTask" table because I was thinking "what happens the next time we need to do this, but for a different task?" If you have a table for tasks and one for the junction table VendorTasks, then this is simple.

eichnerm Old Hand Points: 373 More actions · Answer 7

Thanks. Scott had a similar idea. It's one that I had never crossed my mind.

eichnerm Old Hand Points: 373 More actions · Answer 8

Thanks. I just replied to your initial post about a minute ago.

ScottPletcher SSC Guru Points: 101258 More actions · Answer 9

b) there could be an issue if the flag in the Task table doesn't match the data in the vendors table. In other words, if the task is marked complete, but not all of the vendors have completed their task. To me that should never happen unless there is a programming bug.

Yes, but bugs do happen, and then you need clean-up code to correct it and, perhaps worse, people start not trusting the result.

If the table is clustered on task and vendor (could be a primary key also, if you prefer), then a sequential scan of rows for a task will be very efficient, very likely less overall overhead than the code required to maintain an all_complete flag.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

eichnerm Old Hand Points: 373 More actions · Answer 10

I am planning on using your suggestion. Frankly I don't know how I didn't see that in the first place. Would this type of selection run efficiently? Am I better off checking the bit flag that you described instead of checking CompletionDate?

Thanks

SELECT t.Id, t.TaskName
FROM Tasks t
WHERE EXISTS
(
    SELECT 1
    FROM VendorsAssignedToTask v
    WHERE t.Id = v.Id
      AND v.CompletionDate is null
);

pietlinden SSC Guru Points: 63709 More actions · Answer 11

I would think so.

Especially if VendorsAssignedToTask is properly indexed. (One would think it would be indexed on both TaskID and VendorID). Best way to check is to show the execution plan.

Oh, silly me... Scott showed the indexing for the 3 tables in his code. =) Try that.

This reply was modified 2 weeks, 3 days ago by pietlinden.

eichnerm Old Hand Points: 373 More actions · Answer 12

Thanks. I am going to have to play around with this.