What would be the best schema to store the 'address' for different entities?

Question

Suppose we're making a system where we have to store the addrees for buildings, persons, cars, etc.

The address 'format' should be something like:

State  (From a State list)
County (From a County List)
Street (free text, like '5th Avenue')
Number (free text, like 'Chrysler Building, Floor 10, Office No. 10')

(Yes I don't live in U.S.A)

What would be the best way to store that info:

• Should I have a Person_Address, Car_Address, ...
• Or the address info should be in columns on each entity,
• Could we have just one address table and try to link each row to a different entity?

Or are there another 'better' way to handle this type of scenario?
How would yo do it?

Answer 1

I'd say to have an AddressType field that is a lookup from a Drop-Down list

Answer 2

I have seen scenarios where the Address is stored in an Address table and then there are many-to-many link tables which store links to addresses from People - there is a separate table for each so that foreign keys can be enforced. Sometimes the link table stores information about the relationship, like primary, ship-to, etc.

I've also seen it where the address is stored in the row of a customer. This results in effectively arrays of addresses for bill-to, ship-to, etc, and it's fine that way. Having dealt with both, I think I prefer having them in their own entities, it allows you to keep history of old inactive addresses pretty easily.

We've used this same technique for phone numbers, where people need to store varying numbers of phone numbers.

Answer 3

I would highly recommend reading 'Data Model Patterns - Conventions of Thought' by David C. Hay. This issue is discussed in depth by the author.
What you have in your design are two broad entities.

1. Address of a geographical location
2. A person/object that resides/belongs to the address

In general, it is not a good practice to combine the address with a person or objects' details in the same table like below

Person(personID, name, gender, addressline1, addressline2)

You could have the following entities in your design

Address(number, street, countyID,stateID)
Party(PartyID, Type)
Person(PersonID, name, dob, gender,...,primaryPartyID)
Car(carID, make, model, ...,primaryPartyID)

The Party is a link between person/car to an address. The primaryPartyID in person and Car tables are foreign keys to the party table. This way, you can share and address between a car and a person. In the event you want to store multiple addresses for each person, you could add a separate m:n table between person and party. The type attribuet for Party can take the following values : 'Person', 'Vehicle' etc...