Changes to existing data structures

Posted on 2004-11-05
Medium Priority
Last Modified: 2010-04-23
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?

Question by:bhlang
  • 3
  • 2
LVL 27

Expert Comment

ID: 12509666
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:

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

LVL 27

Accepted Solution

Dabas earned 800 total points
ID: 12509670
> 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

LVL 14

Expert Comment

ID: 12510203
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".
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 12511500
So the Linking Table would have be as follows?
Default (boolean?????)
LVL 27

Expert Comment

ID: 12511526
Yes. YOu got the idea

Author Comment

ID: 12513536

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Screencast - Getting to Know the Pipeline
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question