Help with table design and relationships

[Richard Wright]

Hi,

I have three tables each containing separate but related information. The
first table is the address table, which is the main table. The second table
is a customer table and the third table is roofinfo.

What I need to do is link any number of customers to one address and any
number of roofinfo to a customer. I believe I want an address table with a
1 to many relationship to the customers table and then a many to many
relationship between the customers and roofinfo tables.

My situation is that I need to display one property address and ALL the
customers and ALL the roofinfo for each customer. I hope this makes sense.

Another way to look at it is I have an address at 123 Main St. with a
customer of Bill's Realty, Bill has two entries about roof inspections that
I did at this address.

I appreciate in advance all help and suggestions that can be offered.

TIA,

Rick

 

[tina]

to figure out what kind of relationship your tables have, do the drill:
one-to-many
one record in TableA may be related to many records in TableB
one record in TableB may be related to only one record in TableA

substitute your table names for A and B.
Note: you always have to test *both* sides of the equation.

many-to-many
one record in TableA may be related to many records in TableB
one record in TableB may be related to many records in TableA

a many-to-many relationships is expressed via a linking table that converts
the relationship into 2 one-to-many relationships, as

one record in TableA may be related to many records in TableAB.
one record in TableB may be related to many records in TableAB.
each record in TableAB has a unique combination of the foreign key values
from TableA and TableB.

also, the relationship must encompass all possibilities. for instance, if
you say that 99% of the time, each record in TableB is only related to one
record in TableA - that still leaves the other 1% that has a many-to-many
relationship. you need to either 1) handle it as many-to-many, with a
linking table, or 2) analyze your data to see if you need to break it down
further into additional tables.

based on the above, and on the info in your post, i'd say that tblCustomers
has a one-to-many relationship with tblRoofInfo. but you know your data
best, so what do you think?

btw, it's unusual to have Addresses as the "one" side of a one-to-many
relationship. that says you can have many customers at the same address, but
only one address for each customer. what happens if Bill's Realty has two
offices? Bill is still your one customer, right? again, you know your data
best, but suggest you analyze it again, testing it against the matrix
outlined above.

hth

 

[Richard Wright]

Hi Tina,

You gave me some sound advice. It seems I need to re-think how I want to
organize the data. I will re-post with additional questions real soon.

Thanks,

Rick

 

[Tim Ferguson]

What I need to do is link any number of customers to one address

If each Address belongs to one Customer, then you need a CustomerID column
in the Addresses table to point to the Customers table. You can of course
make as many Addresses as you like have the same CustomerID value.

and any number of roofinfo to a customer.

As above: if each Roofinfo belongs to one Customer, then you need a
CustomerID column in the Roofinfo table.

On the other hand, if each Roofinfo may be shared with more than one
Customer, then you will need a new table to track the relationship:

HasARoof(CustomerID, RoofInfoID, PercentageOfCost, IsPrimary)

The Primary Key is made up of the pair (CustomerID, RoofInfoID), as each
Customer will only have one set of information about a particular roof.

These are typical 1-to-many (first and second examples) and many-to-many
(third example) schemas.

Hope it helps


Tim F