SQL Server database design with foreign keys

by Patrick Rennings   Last Updated March 13, 2018 08:06 AM

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!

Related Questions

Multiple Cascade Paths (Not really) - Sql Server

Updated July 09, 2015 17:02 PM

Database can be referenced by tables

Updated August 03, 2017 16:06 PM

How to use a foreign key?

Updated January 12, 2017 08:02 AM