Changes to existing data structures

I'm using Visual Basic .Net and Microsoft SQL or Access databases.

Let's say I have a Customers table in my database as follows:
----------------------------------------
ID -Customer ID
Name - Customer Name
AddressID -refers to an Address record
Status - Customer Status
... more Customer info fields
----------------------------------------

Let's also say I have an Addresses table in my database as follows:
----------------------------------------
ID - Address ID
Name - Contact Name for this Address
Company - Company Name for this Address
Address1 - Address Line 1
Address2 - Address Line 2
Address3 - Address Line 3
City - self evident
Region - State/Province
POCode - Post Office Code
... more address info fields
----------------------------------------

Now let's say that I need to allow a Customer to have as many addresses as they want (customer has home and work addresses).
At the same time an address may apply to more than one customer (people living at the same address) - should I allow this, or should the address be duplicated in the database?

How would you design the database to handle this? Where can I find resources on the internet that discuss this further?

How do you specify which address is the "Default" address for the customer?


bhlangAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DabasCommented:
Hi bhlang:
This is a typical situation of a many to many relationship.
The best way to deal with a many to many relationship is to have an intermediary table between the two consisting of just two fields:
CustomerID
AddressID

Thus if you have customers C1, C2, C3, C4 and Addresses A1, A2, A3, A4
C1 has addresses A1 and A2
C3 also has A1

This table will have the following entries:
C1, A1
C1, A2
C3, A1

Dabas
0
DabasCommented:
bhlang:
> How do you specify which address is the "Default" address for the customer?
You can add a third field to the proposed table to handle this situation

Dabas
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ptakjaCommented:
Dabas is right. This 3rd table is the best way to handle many-to-many relationships in a relational database. It is commonly called a "Linking Table".
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

bhlangAuthor Commented:
So the Linking Table would have be as follows?
----------------------------------------
CustomerID
AddressID
Default (boolean?????)
----------------------------------------
0
DabasCommented:
Yes. YOu got the idea
0
bhlangAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.