|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Question about the best way to relate some tables.I am creating a database that contains persons, vehicles, addresses and some other information. A person may have several addresses. The same with vehicles. That's why I'm going to put all the information in different tables. I don't store the address and vehicle information inside the Persons table. In this project is very important to find commonalities between data. For example, if 2 persons share the same vehicle, the system will tell you that. My question is the following. For this particular example that I just gave, should I use the same vehicle record for both persons or I just should create a different vehicle record with the same data for both? If I don't 'share' the same vehicle record I would have a relation 1 to many from persons to vehicles. However if I 'share' it the relation would be many to many. I mean, a person can be related with several vehicles and 1 vehicle (the same physical record) can be related with several persons. The same would happen with addresses. I'm a little bit confused about this. What do you guys think it would be better for this project? Thanks a lot. If you are confused about how to deal with many-to-many, then
this is the basic design problem and is usually resolved with the help of intermediate tables, sometimes called 'junction tables'. You may want to google this term. Star wrote: Show quote > Hi, > > I am creating a database that contains persons, vehicles, addresses and > some other information. > > A person may have several addresses. The same with vehicles. That's why > I'm going to put all the information in different tables. I don't store > the address and vehicle information inside the Persons table. > > In this project is very important to find commonalities between data. > For example, if 2 persons share the same vehicle, the system will tell > you that. > > My question is the following. For this particular example that I just > gave, should I use the same vehicle record for both persons or I just > should create a different vehicle record with the same data for both? > > If I don't 'share' the same vehicle record I would have a relation 1 to > many from persons to vehicles. However if I 'share' it the relation > would be many to many. I mean, a person can be related with several > vehicles and 1 vehicle (the same physical record) can be related with > several persons. The same would happen with addresses. > > I'm a little bit confused about this. > What do you guys think it would be better for this project? > > Thanks a lot. Sericinus hunter wrote:
> If you are confused about how to deal with many-to-many, then Thanks for your answer. Yes, I am used to work many-to-many tables.> this is the basic design problem and is usually resolved with the help > of intermediate tables, sometimes called 'junction tables'. You may > want to google this term. I was just wondering what is the best to use for my particular problem. If it would be better to have one-to-many or many-to-many. I still don't see it very clear. Star wrote:
> Sericinus hunter wrote: The choice you are proposing - many-to-many vs one-to-many - actually> > If you are confused about how to deal with many-to-many, then > > this is the basic design problem and is usually resolved with the help > > of intermediate tables, sometimes called 'junction tables'. You may > > want to google this term. > > Thanks for your answer. Yes, I am used to work many-to-many tables. > I was just wondering what is the best to use for my particular problem. > If it would be better to have one-to-many or many-to-many. I still don't > see it very clear. has little to do with logical design. Referential constraints are implemented with foreign keys and a foreign key always references exactly one row in the parent table. The real question is what are you trying to model? A "many-to-many relationship" should exist in your conceptual model and your business logic if it exists in the real world scenario you are modelling. That's something you know more about than we do. You asked whether you should duplicate data in a table. Why would you want to store redundant data and destroy integrity that way? -- David Portas SQL Server MVP -- Create separate tables for Persons, Vehicles and Addresses as
dictionary or lookup tables. Give each an autogenerated ID number. Create a table to track Persons and Vehicles and another table to track Persons and Addresses. For example, for the Persons and Vehicles table - PersonId VehicleId The combination of the two columns would be your primary key. You could then create views to identify where one person had multiple vehicles or a vehicle has multiple persons. Create view VwMultPersonsPerVehicle as select Vehicle, count(Persons) PersonCount from PersonVehicle group by Vehicle having count(Persons) > 1 Star wrote: Show quote > Hi, > > I am creating a database that contains persons, vehicles, addresses and > some other information. > > A person may have several addresses. The same with vehicles. That's why > I'm going to put all the information in different tables. I don't store > the address and vehicle information inside the Persons table. > > In this project is very important to find commonalities between data. > For example, if 2 persons share the same vehicle, the system will tell > you that. > > My question is the following. For this particular example that I just > gave, should I use the same vehicle record for both persons or I just > should create a different vehicle record with the same data for both? > > If I don't 'share' the same vehicle record I would have a relation 1 to > many from persons to vehicles. However if I 'share' it the relation > would be many to many. I mean, a person can be related with several > vehicles and 1 vehicle (the same physical record) can be related with > several persons. The same would happen with addresses. > > I'm a little bit confused about this. > What do you guys think it would be better for this project? > > Thanks a lot. For example see:
http://www.intelligententerprise.com/010101/celko.jhtml -- David Portas SQL Server MVP --  |
|||||||||||||||||||||||
Other interesting topics