Stephan van Hulst wrote:I love using natural/compound keys, but I will concede that it can be a pain if the keys are big (long strings or many components) and have to be used in many other tables, causing those tables to bloat.
I don't like using surrogate keys if a row can be identified uniquely by natural properties, but it's important to consider issues like how well the keys can be indexed or how large they are.
Hmmm. I still reckon that it's a "how" problem inherent to the database or software you're using.
Any relational database worth its salt should allow you to define relationships - especially hierarchical ones - without specifying
how to do it - or at the very least it should hide the implementation detail from you.
All I should have to do to create a 1:many relationship between two tables is specify the "additional" portion of the key relative to its parent. I shouldn't have to worry
how the database stores them or their associated rows. That is, after all, what they're there for.
Progress already had it 20 years ago in its data language via an "OF" construct, which you could use when creating or retrieving:
ie,
create Item OF Category ...
or
for each Item OF Category ...
Sadly, SQL took over the frigging universe, and it doesn't translate well.
I guess I'm up to 4 cents now. :-)
Winston