Design complexity question and its impact. Maybe slightly OT.

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:48 CDT