If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with table design and relationships
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 |
#2
|
|||
|
|||
Help with table design and relationships
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" wrote in message ... 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 |
#3
|
|||
|
|||
Help with table design and relationships
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 "tina" wrote in message ... 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" wrote in message ... 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 |
#4
|
|||
|
|||
Help with table design and relationships
"Richard Wright" wrote in
: 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 |
Thread Tools | |
Display Modes | |
|
|