June 27, 2011 at 9:46 am
I have a super table called Instrument. I have a sub-type table called BondInstrument. Relationship is one-one. PK is a column called InstrumentId.
I also have a table called InstrumentEvent which is one to many to Instrument. Should it reference the Instrument table or the BondInstrument table? I'm not really sure where my referencing tables should reference.
June 27, 2011 at 10:17 am
Hm, not sure I could quantify WHY but I would link to the base table instead of the sub tables, basically because the events are related to the base table directly and the records in the sub table are directly related to the base table as well. In my mind I see a wheel with spokes. With the base table at the center.
CEWII
July 1, 2011 at 1:48 pm
keymoo (6/27/2011)
I have a super table called Instrument. I have a sub-type table called BondInstrument. Relationship is one-one. PK is a column called InstrumentId.I also have a table called InstrumentEvent which is one to many to Instrument. Should it reference the Instrument table or the BondInstrument table? I'm not really sure where my referencing tables should reference.
I'm with Eliot, I'll go against the base table to enforce RI.
Out of curiosity, may I ask why BondInstrument table was detached from Intrument table? blame it to my ocd but I have problems digesting 1:1 relationships 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 4, 2011 at 3:45 am
Out of curiosity, may I ask why BondInstrument table was detached from Intrument table? blame it to my ocd but I have problems digesting 1:1 relationships 🙂
Because BondInstrument is a subtype of Instrument, in OO you might say that BondInstrument inherits from the base class Instrument. Attributes common to all instruments (not just Bond instruments) go in the Instrument table. Attributes unique to bonds go in the BondInstrument table. I could have other types of instruments: FutureInstruments, OptionInstruments, EquityInstruments, etc, each with their own unique attributes.
July 5, 2011 at 5:38 am
keymoo (7/4/2011)
Out of curiosity, may I ask why BondInstrument table was detached from Intrument table? blame it to my ocd but I have problems digesting 1:1 relationships 🙂
Because BondInstrument is a subtype of Instrument, in OO you might say that BondInstrument inherits from the base class Instrument. Attributes common to all instruments (not just Bond instruments) go in the Instrument table. Attributes unique to bonds go in the BondInstrument table. I could have other types of instruments: FutureInstruments, OptionInstruments, EquityInstruments, etc, each with their own unique attributes.
I see, in that case there is not a 1:1 relationship between Intruments table and WhateverInstruments tables but a 0-1:1 one which makes things a little different. I can live with it. 🙂
Thank you for the clarification.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 5, 2011 at 6:57 am
I see, in that case there is not a 1:1 relationship between Intruments table and WhateverInstruments tables but a 0-1:1 one which makes things a little different. I can live with it. 🙂
Of course, you are right, I was sloppy in my definition. The only other reason I can think of to use a strict 1:1 is for performance, by splitting commonly queried columns in one table and less often returned columns in another table. From a logical design point-of-view I'm not sure why you would want 1:1.
0-1:1 is a different matter, of course. 😀
July 5, 2011 at 2:38 pm
keymoo (6/27/2011)
I have a super table called Instrument. I have a sub-type table called BondInstrument. Relationship is one-one. PK is a column called InstrumentId.I also have a table called InstrumentEvent which is one to many to Instrument. Should it reference the Instrument table or the BondInstrument table? I'm not really sure where my referencing tables should reference.
If Instruments of type other than Bond can have Events, then the InstrumentEvent table must reference Instrument. Even if the business requirements say that only Bonds have events, then I'd consider that a specious claim and still reference Instrument.
If you know for a fact that only Instruments of type Bond will ever have Events, then the InstrumentEvent table should reference BondInstrument. However, I'd want to call the table BondInstrumentEvent.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply