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.

The scenario

  • 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.

My considerations

Having "Index Tables" to link a customers and 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.

Information structure

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

Further considerations

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.