Table RElationship Question

[Sanjay4Dbase]

There are two tables: Driver_Table and Car_Table.

Driver_Table has driver_name , age etc columns. Each Driver in the Driver_Table can have multiple cars associated. Car_Table has car_model, color etc.
Now need to establish the relationship between these two tables and where the FK PK goes.

[skhanal]

You have to create a new table to establish the relation, it will have PKs of both driver and car tables as FKs.

[PANTHER3]

Driver_table has details of driver ,age etc and each driver can have multiple cars. Therefore relationship between driver _table and car_table is 1: Many.driver_name (primary_key)should have unique id and which should be mapped to unique car id in car_table.(foriegn_key)

[jnjulian]

The problem proposed above is incompletely defined. We are told that a driver can drive multiple cars... but nothing is said about the reverse of the relationship. We need to know if one car can have multiple drivers?

If yes, then you will need a join table with a composite unique key made up of foreign keys to drivers and cars tables. Each row would identify the drivers who drive each car. Alternatively... you could choose to identify the driver of the car in relation to some other piece of information. For example, if you stored the data for the trips taken by a car (a TRIPS table), you could use that as a join showing that driver A drove car B on trip 1, and drove car F on trip 2, while Driver C drove car B on trips 3 and 4 (In the trips table, you would store the PK (trip id) and the FKs to drivers and cars).

But if one car can only have one driver, then the correct model would be to have the primary key in the drivers table stored in the cars table as a foreign key identifying which driver drives that specific car.

Be careful when gathering requirements and modeling data not to make assumptions about the data relationship (as the previous two posters did) - always ask the question, even if it seems trivial or obvious what the relationship is. All data relationships are ALWAYS bi-directional - and you need to define the data in BOTH directions before designing a solution.

Regards,

~JJ