See the following link for the (total) databse design: https://sqldbm.com/Project/SQLServer/Share/_AUedvNutCEV2DGLJleUWA

I am not used to the database design first and then build it. But... for everything there is a first time... and I try to do make a database that is using standards and is using the power of SQL Server the correct way.

I am trying to create a database design where foreign keys are used.

The data for the tables documentsets_metadata, documentset_casenumbers, documentset_costs, documentset_controls & link_id cannot have data for documentset_id if there isnt a row with the same ID in documentsets.

The tables routes, route_details, cotg & planetpress_controls can have data without the documentset_id id being present in documentsets.

All the tables mentioned above (except for the documentsets_metadata table) have a 1:m relationship (Where documentsets is the parent table).

The tables below documentsets are the following: subsets, subdocuments, subdocuments & documents.

the tables subsets, subdocuments, documents all have an m:m relationship so I thought the best way to create a database to get unique and correct that is to put a link table in between called subdocuments_documents.

Now I thought within this link table I only put foreign key references so that all the data in this table is always present in the subtables and when a row in subset is removed all the data in those subtables are also removed.

Do you guys think I am on the right track or I am totally going the wrong way?

Any help is appropriated!

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.