fk_tc_01
fk_tc_02
fk_tc_03...
fk_td_01
fk_td_02
fk_td_03...
"Nuno Souto" <nsouto_at_nsw.bigpond.net.au.nospam> wrote in message
news:3ae959fe.1348607_at_news-server...
> OK folks, picture this simplified description:
> (types of data immaterial in this, PK is abbrev of PRIMARY KEY and FK
> is abbrev of FOREIGN KEY, of course)
>
> TableA{
> hname,
> taid,
> tadata1
> constraint PK_TA (hname,taid)
> }
>
> TableB{
> hname,
> tbid,
> tbdata1
> constraint PK_TB (hname,tbid)
> }
>
> TableC{
> hname,
> tcdata1,
> constraint FK1_TC (PK_TA)
> constraint FK2_TC (PK_TB)
> constraint PK_TC (FK1_TC,FK2_TC)
> }
>
> TableD{
> hname,
> tddata1,
> constraint FK1_TD (PK_TA)
> constraint FK2_TD (PK_TC) <itself made up of PK_TA,PK_TB!>
> constraint PK_TD (FK1_TD, FK2_TD)
> }
>
> Suggestions for how to name the columns implementing the FKs in TC and
> TD ?
>
> taid and tbid is not a major problem, but the repeated hname is. Any
> particular preferences on naming convention so that we don't end up
> with Designer 6-style *loooooong* names for columns? hname is needed
> for something else completely different, but causes no end of naming
> problems.
>
> I'm using <hname_TA> and <hname_TB_TC>, but even this gets too long if
> we have more levels down of relationship hanging off TC and TD. It
> gets very long and confusing after just three levels and that is using
> abbreviations all over the place.
>
> I know, I should make PK_TC out of additional unique columns created
> for this purpose inside TC, instead of using the two FKs concatenated
> as my PK. But I'd like to keep it consistent if at all possible.
> Additional columns just for the purpose of making it simple to name
> FKs and PKs of dependent tables will eat into indexing, sequences,
> complexity of managing them via triggers (which I'll need for
> something else), etc.
>
> And even that doesn't help with the example I mentioned of "TD hangs
> off TC and also TA". It's a kludge that only results in need for even
> more indexes and sequences and that can start to eat into my
> insert/update/delete performance.
>
> The whole thing gets very complex if we have more than two columns
> making up the PKs and FKs. And say 4 or 5 tables hanging off each
> other...
>
> I've simplified wherever I know that I can. For example, I've put
> only the part of PK_TC that I know is relevant for TD, because TD
> instances will only occur for the same PK_TA that it hangs off from
> both TA and TC (via its FK1_TC). But I don't feel comfortable with
> it. It doesn't read correctly for anyone looking at the schema only,
> with no diagram.
>
> Have been trying to come up with some sort of convention that is easy
> to pick, doesn't result in huge column names and doesn't require me to
> add "local ID" columns for related tables instead of the proper
> combination of FKs as the PK.
>
> Complex problem, ages old and AFAIK without a satisfactory solution.
> Any ideas, thoughts, etc will be greatly appreciated.
>
> If you feel it's better to discuss this offline, fine by me.
>
> Cheers
> Nuno Souto
> nsouto_at_bigpond.net.au.nospam
> http://www.users.bigpond.net.au/the_Den/index.html
Received on Fri Apr 27 2001 - 07:29:45 CDT
