How i make many to many relationship in database and interface

[ahm1985]

Hi guys i want to make fleet management program to cars in my organization but in erd diagram i face problem I have two table
Driver table
Diver No
Name
Address
Car No

Car table
Car No
Driver No
I want to make design many to many relationship means one car can driven by many drivers and one driver can drive more cars.
My question how i represent this relation in database and interface (meaning are i make table to driver and table to car only in case that relation is many t many or what i do )

 

[djj55]

What sql server are you using?

Look at CROSS JOIN.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

Looks to me like you should have 3 tables.

Driver
--------
DriverNo
Name
Address

Car
---------
CarNo
Make
Model
Year

CarDriver
----------
CarNo
DriverNo

The primary key for the Driver table should be DriverNo.
The primary key for the Car table should be CarNo.

The primary key for the CarDriver table should be CarNo,DriverNo (this means it is a composite key because it contains multiple columns). You should also create foreign key relationships between CarDriver.CarNo and Car.CarNo and between CarDriver.DriverNo and Driver.DriverNo

By creating the foreign keys, you are effectively preventing data in the CarDriver table from containing data that does not appear in the other tables. Basically, if you have 3 cars in your system numbered 1, 2, and 3 then you will not be able to add a row to the CarDriver table with CarNo = 7 because SQL Server will give you a foreign key constraint error.

This will prevent your data from getting corrupt.

There is a minor "problem" that you need to be made aware of. When you have a foreign key, you cannot delete data from the base table if that data exists in the related table. For example, suppose you have cars 1, 2, and 3. You decide to sell car 2 and delete it from your table. If there are any rows in CarDriver with CarNo = 2 and you do "Delete From Car Where CarNo = 2" then you will get an error. You must first delete the rows from the CarDriver table and then delete the rows from the car table.

Of course, there are also ways to automate this process. You could cascade delete or even use a trigger. I think it might be better to delay discussions regarding these options until you understand the rest of what I have said here.

Hope this helps.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ahm1985]

OK thank you very much i using sql server 2005
but in interface when i prepare forms in program
when i need to insert data are i made
driver form
car form
car driver form this actually what i need
thanks

 

[johnherman]

George , gmmastros, has given you the best solution. It involves a "relation" or "bridge" table between he car and driver tables. From a data entry point of view, you would enter either the drivers or cars first, all of them. Let's say cars. After all the cars are entered, you would start entering drivers. After you get the basic facts and attributes about the driver itself, you would then link the driver to one or more cars (or zero). Each car that the driver is associated with would get a CarDriver record.


==================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright