Multiple Addresses Database Design

Howdy All,

I want to design a database that has a single table that holds addresses from several different entities. e.g

Contacts
--------
ContactID

Sponsors
--------
SponsorID

Events
--------
EventID

Now a Contact can have multiple addresses, but only one is the preferred. My initial thought is to create lookup tables for each of the tables or just make the following table design for addresses
addresses
--------
AddressID EntityID Preferred(Bit Field) Street City Zip State...

I want to maximize effeciency. Any ideas are greatly appreciated.Does an address have a separate existence from the entity joined to it?

You could have the structure

Address
AddressID, ....

EntityAddress
EntityID, PreferredAddress, AddressID

Entity
EntityID, EntityType

Sponser
EntityID, ...

Contact
EntityID, ...

Then you can show that several entities can have the same address.|||You can do the following for example

Customer ID, Customer Name
Contact ID, Customer ID (FK), Address Line 1, Address Line 2, Prefered
Contact Address ID, Contact ID (FK), ..........

This will help you to make multiple addresses for each contact and multiple contacts for each customer.

Regards,

Firas arramli|||in order to minimize maintenance tasks you could have just 3 tables:

EntityMaster (EntityID, EntityTypeID, etc.)
EntityTypes (EntityTypeID, EntityDescription) -> static table
Addresses (AddressID, EntityID, EntityTypeID, PrimaryAddressFlag, etc.)

however, you need to keep in mind that by minimizing maintenance you may be stepping on your performance. on your first post you mentioned that you're after efficiency. by having a separate address table for contacts, sponsors, and events you'll achive faster performance vs. combining them all into addresses table.