I am working on a database based application and am also looking for some feedback on how to organize the relevant tables. The application handles customer project info and the projects technical drawings.
- Each Project is made up of n quantity of Drawing Sets, which have n quantity of Drawings with n quantity of Revisions each made by Employees "a", "b", and "c".
- Each Project needs to be associated with the Customer it belongs to and the Employees who worked on the project, both of which have arbitrary ID Numbers.
Having "Index Tables" to link a
employees table with a
projects table seems like an obvious choice but since the projects essential have a nested structure I have become unsure about how to continue.
If working up-to-bottom, a Revision would have a unique ID structured as
- (Project No.)-(Set No.)-(Drawing No.)-(Revision No.)
and a Drawing ID would be
- (Project No.)-(Set No.)-(Drawing No.) and so on.
The Numbers are made up as follows:
- Project: Sales Order Number, Line Item, Release
- Drawing Set: Type, Set Sub Type
- Drawing: Page Number, Section Number
- Revision: Rev Number, Minor Rev, Build Rev
All the Projects would go into a table just fine with a compound primary key being made up of the Sales Order Number, Line Item, and Release, the Project ID. But If a Drawing Set's primary key was a compound key made up in part by a compound key from another table the number of columns start to get out of hand.
This is where I get torn with using "Index Tables" to link Drawings with Drawing Sets and Revisions, or make a new
drawings table and a
revisions table for each Drawing Set or just for each Project. I have read that surrogate primary keys would be better than natural keys but for some reason I still feel like having project-specific tables is better than grouping all Drawings from all Projects into a single table. I'm fairly new to this whole database thing so I'm not even sure if another solution even exists but some feedback on how you would approach this would be helpful.