There always comes a time in database design in which you have to contemplate the "right" way to do things vs. a technique that may not be quite correct, but will bring other benefits to the (sorry for the pun) table. Usually this relates to speed - fewer joins means zippier response. Hopefully this is kept to a minimum and the benefits are well documented.

But, to me at least, it's never fun (and not always clear) which is truly the best way. I always feel a bit torn. It's easy to find information regarding how to do it "right", but not so easy to find guidance on when to bend/break the rules.

Today I'm redesigning someone else's database. It has some common problems that are easy enough to address, but some of the other issues take me into that gray area. However, I'm a bit tired of the gray area and would like to revisit how I make these decisions. One specific question would be how to design a CUSTOMERS table where the customer may have multiple child companies and each of those may have multiple addresses. This is something you can keep fairly simple, but then you have to put up with some redundant data, or you can take it to the nth degree and then you have more complex joins. Another question is when do you combine entities into one table, such as a codes table that has a field indicating the type of code vs. separate tables for the differnt code types or a table that might contain customers and vendors with a code indicating if it was a customer or vendor (this is an extreme example that I've always avoided, but it's been done).

Russell Campbell