Database Design - Four-Level Parent-Child Relationships

This might get a little complicated. Here goes.

Say we have a parent-child relationship like this:

A Project contains many Tasks. A Project may also have any number of Revisions.

And the database schema for these tables look something like this:

Projects:
ProjectID
ProjectName

ProjectRevisions:
ProjectRevID    
ProjectID
ProjectRevName

Tasks:
TaskID
ProjectRevID
TaskName
HoursToComplete

The tasks table is populated from another table, TaskDescriptions, which contains the master list of tasks.

My employer also wants subquotes - meaning an individual can quote his own effort seperate from the master quote. Each time a ProjectRevision takes place, the Subquotes must be redone, and all old Revisions and SubQuotes m开发者_开发知识库ust be kept for future reference.

How would this look in a table schema? In my eyes, this is essentially a four-level list: A project contains a list of Revisions, which each contain a list of SubQuotes, which each contain a list of tasks.

I may be overthinking this, but any help is greatly appreciated

My employer also wants subquotes - meaning an individual can quote his own effort seperate from the master quote. Each time a ProjectRevision takes place, the Subquotes must be redone, and all old Revisions and SubQuotes must be kept for future reference.

How would this look in a table schema? In my eyes, this is essentially a four-level list: A project contains a list of Revisions, which each contain a list of SubQuotes, which each contain a list of tasks.

Assuming there won't be any instances where a subquote could be related to more than one user:

SUBQUOTES table

• SUBQUOTE_ID, pk
• PROJECT_REV_ID, fk
• USER_ID, fk
• [supporting columns]

I don't see any other changes necessary to the existing data model.

What is the definition of a revision? Depending on that I would probably go for a temporal design. So instead of a Project having multiple revisions, the project table would store the revisions. You can accomplish this by adding a column to track the previous primary key, the revision start date and revision end date. When a revision is made, the old Id would be linked back to from the new record. The new record would have a start time of now and an empty end date. The old record would have had an empty end date, but that would need to be updated to now as well. The old record will still point to all of the old sub quotes.

Project
-----------------
Id
RevisisedFromId
RevisionStartDate
RevisionEndDate
RevisionNumber (optional, this can be calculated)


SubQuote
-----------------   
Id
ProjectId (when a new revision is made, this will still point to the old revision)

In this model, the Project table has a surrogate ProjectID primary key (auto-increment) and a NaturalKey which has to be unique to a project and can not change (like "Pave My Driveway 25764").

When a new revision is issued, a new line is inserted into the Project table and ProjectID is incremented, however the NaturalKey is copied over. Revision number is updated and RevisionStatus field in the new row is set to "current" and to "expired" in the previous row. At this point all tasks point to the old revision and all quotes are pointing to those tasks -- so the history is preserved. It is easy to track revisions (collect all ProjectIDs) using NaturalKey.

When a task is "carried-over" to a new revision, it is copied into a new row with a new primary key and the foreign key pointing to the new ProjectID. This way history is preserved too.

All quotes now have to be done for new tasks, or copied over to a new row with foreign key pointing to the new TaskID. This way quote history is preserved too.

Or a task may have zero or one subquote. Depends very much on your context.