Hey,

I have a database that contains users, companies, orders with billing addresses etc. I would like to have a table that just contains addresses and where all these tables can refer to.

The problem is that sometimes the "recipient" part of the address is the first name + the last name of a table (e.g. user), sometimes it's the company name or it might be something totally different so there has to be a column in the address table which is called "recipient".

This all creates a lot of overhead. I would be glad for any suggestions (a nice design pattern maybe ) to solve the problem!! Thanks a lot in advance!

Best,
Henning

you can solve this problem easily by ~not~ having an address table

just leave the address columns in the tables which currently have them

if you still want a separate address table, then i would put the onus on you to explain why

and saving space, in this day and age of terabytes for fifty bucks, is no longer a valid reason

So how would you store multiple addresses per person then?

that's an entirely different problem, innit

i just thought it's better db design - otherwise i would have "street, postal code, city etc." in several tables. Multiple addresses could easily be created by many-to-many relationships or by giving the user table multiple reference id's to the address table. But still - idk if i'm adding unnecessary complexity here...

you are, and for what benefit?

I guess you're right...I will just put address fields in multiple tables - thanks a lot for the input!

Hi

IMHO it's better to have an Address Table.
This is a 'normalised design' and also offer other benefits.
For example, you can use a commercial package, such as QAS or Trillium to validate Addresses if they are in one Table.
The Data Model on this page of my Database Answers Web Site shows an example oif what this looks in practice :-
Customer Addresses Data Model

HTH

Barry Williams
Principal Consultant
Database Answers

but so is having address fields in each entity

my classic rejoinder for this argument is that you hardly ~ever~ see anyone designing a first names table, even though many entities are called "john" -- and yet developers are so quick to have an address table, when the amount of sharing of the same address is substantially less than people called john

you'd need a separate address table only if either (1) an entity needs more than one address (see earlier post re addresses per person), and then it's still not enough reason to have a many-to-many relationship instead of just a one-to-many relationship, or (2) there is a strong need to share a single address amongst many entities, or (3) you were interested in the addresses themselves, regardless if there was anyone or any entity at that address

and drawbacks as well -- in your model, you need a three-table join to find customers in a given city

Having an "Address" table is a huge benefit from an application perspective when you have to do "Address Normalization" to meet the UPU requirements to get reduced international rates. If you have infinite money, then this isn't a constraint but nearly every company that I work with has a finite budget and those budgets tend to be jealously guarded lately.

Abstracting out addresses from a small database (under 500 Gb) and especially one that only deals with a few countries (many only deal with North America) is probably counter productive. When you start dealing with big problems (dozens or hundreds of countries) and lots of data (Terrabytes), the change in scale forces you to think differently.

-PatP