While working on designing a database for a project I'm working on, I find myself obsessively wanting to abstract data common to multiple tables. But how much is too much? Of course I want to find a good compromise between slick table structure and simple SQL queries, but I'm sometimes not sure.

The current example that I'm weighing is an Address table. This is a relatively simple database, and ultimately there could be around 6 or 7 (maybe a few more in the worse case scenario?) tables that would need address fields.

So what's the general consensus? Is it worth abstracting the Address data to its own table? Or is this a case when it would be simpler to just stuff those fields in each table?

Britt

This is a decision that needs to be made after considering the pros and cons for your specific situation. There is no "right answer".

By creating a single Address table you are saying that Address is an entity in its own right, that may be associated with various other entities. This would allow you to query based on Address, e.g. "what people/organisations/etc. are located at address x?" It also means that you only have to enforce the Address business rules once, and that addresses are handled consistently everywhere.

On the other hand, if for your purposes address is merely a few attributes with little in the way of associated rules, and you don't want to query by address across entities, and you don't mind the duplication of coding effort, then the other approach may be fine.

Having said all that, I'd probably go for the Address table. Right now you may not have need of its advantages, but as requirements change you may find you do.