Sign in to follow this  

Preserving 1:Many Relationships in DB

This topic is 2820 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hi, (Using MS SQL 2008, however this is more of a database design question whose answer will mostly transcend particular RDBMS implementations) I am trying to figure out what "best practices" might dictate in this given situation... I'm trying to preserve (store) a 1:many relationship between records in two different tables. Let's pretend I'm writing an automotive-oriented software program, and in the database I'll have two tables, [cars] and [drivers]. The [cars] table contains all sort of information about the vehicle itself, whereas the [drivers] table contains info regarding the different drivers available to the system (personal info, etc.). For the sake of this example, let's also pretend there is a 1:many relationship between cars and drivers, i.e., 1 car can be driven by 1+ drivers, but each driver may only be assigned to 1 car. The [cars] table might look like this: [cars].[car_id] --> the unique ID (primary key, auto-inc, etc.) [cars].[car_year] --> year car was made [cars].[driver_ids] --> an NVARCHAR(MAX) value comma-delimiting the unique IDs of all the drivers assigned to the car The [drivers] table might look like this: [drivers].[driver_id] --> primary key auto integer ID [drivers].[driver_first_name] [drivers].[driver_last_name] And so, one particular car record might have the following field values: [cars].[car_id] = 1 [cars].[car_year] = 2005 [cars].[driver_ids] = "1,3,6,7" ...Where it is implied that drivers with [drivers].[driver_id] of 1, 3, 6 and 7 are all "mapped" (assigned) to this particular car record. If I were to set things up this way, with the 1:many relationship being stored in the form of a string of comma-delimited IDs, then I will also need to build a custom function that might be called IS_IN_SET(int, nvarchar(max)), where it would return TRUE or FALSE if a given integral value can be found inside the numerified string: Ex: IS_IN_SET(110, "34,110,56,7,40") would return TRUE IS_IN_SET(111, "34,110,56,7,40") would return FALSE This has *got* to be a common problem database engineers run into, and there *must* be a more eloquent and sophisticated way of doing this. Please don't read too far into the cars/drivers example, I'm simply trying to illustrate a situation where records in Table A need to each keep references to 1 or more other records over in Table B. Thanks for any and all input/suggestions! ~ply

Share this post


Link to post
Share on other sites
There is definitely a better way! Create a third table with columns car_id and driver_id. Depending on your DBMS, you should define car_id and driver_id as foreign keys - I'm fairly sure MSSQL supports this. Your SQL for selecting all cars and the drivers associated with them (without using JOIN syntax) would be something like:

SELECT * FROM cars, drivers, cars_and_drivers WHERE cars.car_id=cars_and_drivers.car_id AND cars_and_drivers.driver_id=drivers.driver_id

As long as you define your keys properly, this should be very efficient.

Share this post


Link to post
Share on other sites
What MNicholson described was a many-many relationship, so any number of cars can have any number of drivers.

For a 1-many relationship is becomes slightly simpler. From you example for every car there is many drivers.

You had the right idea driver_ids field however you made the mistake of putting it on the wrong table. Since each driver can only have 1 car, then you can add car_id to the drivers table.

The [cars] table might look like this:
[cars].[car_id] --> the unique ID (primary key, auto-inc, etc.)
[cars].[car_year] --> year car was made

The [drivers] table might look like this:
[drivers].[driver_id] --> primary key auto integer ID
[drivers].[driver_first_name]
[drivers].[driver_last_name]
[drivers].[car_id]

And so, one particular car record might have the following field values:
[cars].[car_id] = 1
[cars].[car_year] = 2005

[drivers].[driver_id] = 1
[drivers].[driver_first_name] = driver
[drivers].[driver_last_name] = 1
[drivers].[car_id] = 1

[drivers].[driver_id] = 2
[drivers].[driver_first_name] = driver
[drivers].[driver_last_name] = 2
[drivers].[car_id] = 2

[drivers].[driver_id] = 3
[drivers].[driver_first_name] = driver
[drivers].[driver_last_name] = 3
[drivers].[car_id] = 1

In this example "driver 1" and "driver 3" both drive "car 1" while "driver 2" drives "car 2"


Share this post


Link to post
Share on other sites
You've got it backwards. Your driver should have a car_id field. Your car record will not have any sort of information about the specific driver.


SELECT * FROM CAR, DRIVER
WHERE CAR.ID = DRIVER.CAR_ID AND CAR.ID = @CarId

Share this post


Link to post
Share on other sites

This topic is 2820 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this