THE SCENARIOI learned about basic database design concepts such as basic CRUD operations, referential integrity, relationships, etc., years ago. I've messed around with databases and used this knowledge in an unofficial capacity over the years while learning about C# and WPF.Now I find myself at the beginning of my first official database design project. I am about to design and write a WPF application for my company. This application is to replace and an old Foxpro 2.6 / Visual Foxpro conglomeration they are currently using.The new WPF application will use the MVVM design pattern and a Repository pattern for data access to the SQL database back-end. I will be converting seven FoxPro 2.6 tables to a new SQL database design:Customers - each customer can have multiple pieces of equipment**Equipment** - each piece of equipment can have multiple testtypes    **TestType1** - each test can have multiple years of test results        testtype1 year1 test result        testtype1 year2 test result     **TestType2**        testtype2 year1 test result        testtype2 year2 test result     **TestType3**        testtype3 year1 test result        testtype3 year2 test result     etc...Right now, the Foxpro customers table has a CustomerID field as the identifier, and the field is repeated throughout ALL of the tables, including the TestType tables for the equipment, along with an equipnumber field.PRIMARY KEYS / FOREIGN KEYSI intend on creating primary keys in all my tables and setting up standard one-to-many relationships between the Customer and Equipment tables and between the Equipment and Test Type tables.That is what I want to do, right?Should I also store a Customer's primary ID in my TestType tables? I know that would make SOME queries easier, but is that proper design?I have worked in a couple of shops and have seen their database designs. To me, it didn't look like they were setting up relationships and enforcing referential integrity using SQL. I think they were doing all that in C# / VB code.Should I setup things like cascading deletions, updates, etc. and just let SQL Server handle it?Is that a maintenance nightmare?  ISDELETED FIELD - Should I or Shouldn't IInitially I thought I'd want to incorporate and UNDELETE feature into the new program. But I'm not sure I want to do that. I was going to have an IsDeleted and DateDeleted fields for all my table records, and have the WPF app update the IsDeleted field to true and enter a date when a record is deleted. Then, in the Admin utilities have a real PURGE feature that can purge by Date Deleted.Am I asking for trouble here?In what scenarios is this a good idea?  A bad idea?  METADATA - Store IDs or actual data?And one final question. When it comes to meta data, (i.e., test type name, result code, etc., should I store the actual meta data value itself with a data record, or a metadata ID and link it back to the metadata table? The latter seems like it would be cumbersome to query and become a maintenance nightmare too. Are there any advantages to storing a reference ID back to a metadata table?I hope I'm not being too broad here. And any advice and suggestions are truly appreciated.
SQL - Design concepts - Relationships - Referential Integrity - Cascading
database design;mvvm
I would endorse @Claude's opinion of not repeating customerId in TestTable, as this is denormalising the data before you have found a good reason to do so. Also, sticking to the rule that the primary key in any table is a single field works well (OK, I might relax that for a 2 field many:many resolving table)As to the isDeleted field, I have a different take.You don't need both an isDeleted flag field and a dateDeleted date field. I would go with just the dateDeleted field. Once you read your data into POJOs, you can easily add an isDeleted() method to the POJO which just checks if dateDeleted is not null. And of course your SQL queries can do the same check.Do you need to keep a history of changes in this table? If so, the dateDeleted field is a good way to go. Otherwise, why not physically delete the record - simplicity is good :)As to the MetaData question, the answer depends on the business requirements. If there is a 1:1 relationship to the parent table, then there is little to be gained by creating a second table. If The relationship is 1:many you need a separate table, and if many:many you need two tables. I would question the name of this table - MetaData in an SQL database has the connotation of describing the database columns (e.g. NUMBER(12,0) or VARCHAR2(166)), so it is a good term to avoid. And a couple of notes on foreign keys:Make sure you create an index on all the foreign key fields. A normalised database is great, but most of your queries will involve joins, and indexes are required to keep performance reasonable.Think carefully about enforcing foreign key constraints in the database. This slows down performance, particularly when deleting data. Many shops turn them on in development databases and turn them off for production. Personally I leave them on in production unless there is a proven performance problem.Best of luck - this was a good question.