June 3, 2015 at 3:04 pm
Hello, I would like the best way, to enforce normal forms on the following scenario:
I'm having 3 tables, let's say:
Table A
Table B
Table C
Table A 1 <-> N Table B
Table A 1 <-> N Table C
However for the same Pkey on Table A only one Fkey from Table B or Table C can be link to it (table B and C are mutually exclusive, they both have field the other table doesn't belong to)
1-
I'm not particularly cheerful to merge Table B & C together to solve the mutually exclusive PKey issue. That way, Table A would require having a new column outside of a key to distinguish original Table B fields from those of original table C. Beside having this kind of structure means being in second normal form maximum that's it.
2-
I thought adding a bit column on Table B & C holding a constant enforced through a check constraint on each table (B = 0 and C = 1). On Table A the Pkey will now hold Pkey plus a new bit column. That new bit column will hold 0 for B or 1 for C.
That way it will preserve Table B & C being mutually exclusive with their own respective fields and also enforce normal forms by having Composite PKey and Fkeys (Original column + bit column)
In other words. Table A Pkey can only be referenced from Table B or from Table C. The same Pkey must not be referenced from both B & C.
Is there a better way to do this?
Thank you
(I've added the 3 tables initial structure setup if necessary, please disregard anything that falls outside the question when looking at the script code)
June 19, 2015 at 6:40 am
Is my description unclear? Is there something missing?
June 19, 2015 at 7:02 am
Aside from the fact that neither TableB or TableC have a primary key defined, what is wrong with the structure in the script you posted?
Neither of the two possible solutions you suggested are better choices than what is already here.
What problem are you trying to solve?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 19, 2015 at 7:30 am
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'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).
I feel I should redo my example to include both scenarios with some data. It will help understanding.
As for the primary keys you're right there's none on child tables. Simply it was faster to write the example.
June 19, 2015 at 8:11 am
Megistal (6/19/2015)
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:
ID FirstName
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.
[Quote]
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!
[Quote]
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.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 19, 2015 at 9:06 am
If a record is changed in TableA, what would need to be changed in BOTH TableB or TableC?
Nothing. Otherwise it's data duplication which is another NF violated (DML anomalies). If something is changed in A, only A gets changed. Same for B and C.
Secondly, this, remove column duplication across multiple child tables if enforced at face value, will likely cause a violation of 1NF.
Grouping sets are violating 1NF and you're right about that. What I'm trying to say about column duplication is similar to the oriented object when a class inherit from it's parent. Avoiding inheritance would mean that Class A would have to be written twice (AB - AC) or B and C merged with multiple unused attributes / properties (equivalent to NULL columns) (both design would be bad).
Class A is the base class
Class B inherit from class A
Class C inherit from class A
Class A hold values that both B & C needs. Class B implements it's own properties / attributes which are independent from properties / attributes of the C class. However how a class is implemented in a compiler guaranties a B class mutually exclusive from a C class when instantiated. It is not the case in a relational model of a database when a B row is added and a C row is added. They can (B row and C row), if not properly normalized in this example, point to the same A row.
I understand that OO world is different from the relational world. I hope I didn't make it harder than it might already be.
Some sample data will surely help.
The more we speak the more I feel it will really help!
June 19, 2015 at 11:20 am
It is not the case in a relational model of a database when a B row is added and a C row is added. They can (B row and C row), if not properly normalized in this example, point to the same A row.
Your existing structure IS properly normalized. If rows in TableB and TableC point to a the same, single row in TableA, what normalization rule does this violate?
And, please stop trying to make a class match a table. It doesn't work.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply