Prevent Foreign Key to become orphan

Olaf,

I tried and I think this is really the best way to go for now.
BTW, I'm using MS SQL 2012


Great!

Just one thing that crossed my mind: Of course these two foreign keys now need to have a less strict foreign key constraint than normal, which allows both a valid foreign key existing in the parent table, but also NULL. But off the top of my head this is not exotic even for normal foreign keys (when they are optional relations to other data). Just a detail.

Bye, Olaf.

I understand. In this abnormal case, it is necessary to go beyond normal solution as well. Anyway, I can do extra validations on the programming side to compensate the missing portion.  Your solution is far better than what I originally thought.

Thanks.

Nice & easy.

Fine, I also thought about separating the two new fields and the computed field into a new table, so the order will just reference this new table and it encapsulates the branch to comm or cons. The only benefit would be, you can replace it by something else later, if you get a better idea and your oder table only has its RefNo field as before.

This kind of relation It's not part of normal ERMs, but SERM (structured entity relationship model) knows something called a generalisation/specialisation relation, which this resembles, as far as I see.

The provided solution is easily extended, if you need a third relation, so I'd say this already is one of the best solutions to it.

To get a more normal design you would need to put comm and cons data into one table and give it a bit column deciding the type of data or add another head table with just the common data of the two, with one of comm or cons table exclusively extending this head data. Then you have a normal relation between orders and this new comm/cons head data.

Bye, Olaf.

Thanks Olaf for that great insights!

Your first suggestion is quite complicated, not so much for me, for the one who will maintain the application.  

Your last suggestion is not very appealing as well because even the "description" which common but define different
meaning.  

The solution I currently considered best is non-complicated and very easy to grasp and maintain by other developers.

Cheers.

I don't know of which third solution you speak of.
I suggested either a little complexer than normal constraint for the foreign keys. I don't think counting the NULLs is hard to grasp.

Then I suggested a redesign. It doesn't necessarily put the common fields of cons and comm into the head table. I don't know what real world concepts comm and cons really model, but both of them seem to be orderable or orders might be comm or cons.

Bye, Olaf.

Olaf,

I considered your 1st comment as the best solution and my original design with check constraint and triggers is the still the closest contender. We need to decide, which is which with my client before we start the program development.

BTW, suggestions in this thread will be considered in the final decision: https://www.experts-exchange.com/questions/28586190/SQL-tables-suggestion-needed.html

Thanks.

I see. I'd always opt for a check constraint rather than triggers. First of all you need to maintain both update and insert triggers for checking the validity, then you interrupt an insert/update and have to put it at the end yourself. A check constraint simply is checked on top of the insert/update.

The triggers have the only advantage, you could change the values coming in by code to conform to the rules and get the insert or update to work correctly instead of failing by the constraint. but in what circumstances does this really make sense? A foreign key will not be typed in by the user, so the only errors you'd correct would be your own column addressing errors, for example. That should show up in testing and should be handled by correct code rather than triggers.

You can of course also write the triggers to do the same as the check constraint: Refuse to insert/update, if the rules are broken. But that's what check constraints are made for. (instead of) insert/update triggers should either compute things on the fly (we also have computed fields for that), do audit trails (logging of changes) - we have transaction log or cdc for that - or merge data (we have MERGE for that) or doing anything else with the data instead of the insert or update. Using it merely, because insert and updates trigger it and your code hooks in at that event moment, is misusing it, if you ask for my opinion.

Bye, Olaf.

Thanks Olaf! I will study your opinion.

BTW,  the Insert validation will be taken cared by the Web App combo box - by limiting the selection based on the contents of the source.

>the Insert validation will be taken cared by the Web App combo box

That's good. But it's still just an exception of the rule to not do things multiple times. Database restrictions for data integrity always are good on top of any business or application logic, as they prevent errors from any source.

Preventing errors redundantly already on the UI level saves you to catching error feedback from the database, but doing something on the application level is no reason to skip the check constraint on the database level, because it's only a current truth the only source of that data is the web app.

Bye, Olaf.

In other words, there are a lot of things needed to be done in order to protect data integrity based on my original DB structure. That's what I'm seeing as well though it looks nice to have a single fK.  

Thanks Again.