Trying to get that structure normalized as much as possible (Having two or more child tables mutually exclusive linked on the same parent table) while preserving a "DRY" column approach (remove column duplication across multiple child tables) and explore what ideas others had to (or tried to) solve this.
I think this is a matter of opinion as to this structure violating DRY.
If a record is changed in TableA, what would need to be changed in BOTH TableB or TableC? The primary / foreign key? If that is the case, then your entire design may need to be re-thought.
Secondly, this, remove column duplication across multiple child tables if enforced at face value, will likely cause a violation of 1NF.
This does not apply to the key values. It applies to values such as a name.
If TableA was as follows:
and TableB was like this:
TableBID, ID, TableBValue, FirstName
and TableC was like this:
TableCID, ID, TableCValue, FirstName
Then you would be violating the DRY rules as well as 1NF.
I'm not a big fan of en EAV model for that (even though it has its uses sometimes), nor having several nullable columns if table B & C are merged together (null when it's a "TableB" row and non null when it's a "TableC" row and vice versa).[/quote]
Good. Stay away form this design!
I feel I should redo my example to include both scenarios with some data. It will help understanding.
As for the clustered index you're right there's none on child tables. Simply it was faster to write the example.[/quote]
Some sample data will surely help.