SQL - Design concepts - Relationships - Referential Integrity - Cascading

Question

THE SCENARIO

I 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 KEYS

I 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 I

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

Answer 1

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.

Answer 2

The answer to your question might depend on general design principles in your team und personal preferences.

I describe my personal preferences:

1. Don't repeat CustomerID in TestTable. This is a premature optimization. Join on Equipment instead. Of course it depends on the database product and the size of your data. You are using SQL Server? Indexes and indexed views may boost the performance.
2. In parent-child-relations (UML: compositions) I use cascading deletes a lot. The application code deletes the parent only. This make life easier when the database is refactored. In optional relations (UML: associations) I use on delete set null.
3. Almost never I use on update cascade because primary keys have to be designed to never change.
4. ISDELETED makes implementation a nightmare as one of the comments pointed out. Think about if a sophisticated transaction handling might meet your requirements. Otherwise something similar to Oracle flashback may help. Change tracking is an option on SQL Server.
5. Metadata: If you identify a 1:N relation with a non primitive (1)-part I would store the (1)-part in a separate table. Sometimes the (1)-part has only few distinct constants of some primitive data type (in your example result code?). If there is no need for additional information as timestamps, descriptions etc. I don't use a lookup table but define a check constraint like resultcode in (0, 1, 5). About performance see point (1) above.