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 year1 test result
testtype2 year2 test result
testtype3 year1 test result
testtype3 year2 test result
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.