Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
zharvey238
Starting Member
1 Post |
 Posted - 2010-03-29 : 13:40:59
|
| Hi,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 carThe [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 TRUEIS_IN_SET(111, "34,110,56,7,40") would return FALSEThis 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!~Zac |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-03-29 : 14:13:33
|
| Storing multiple values in a single column is a really bad idea for several reasons:1. The table is not in first normal form.2. Queries and updates are hard to write.3. Referential integrity cannot be enforced by the database engine.You have a couple of better choices:1. Add the FK column car_id to the Drivers table to represent the relationship.2. Add an intermediate table with the combination of car_id and driver_id to represent the relationship. I would go with this, since it sounds absurd that the relationship of car to driver is really 1:M and not M:M.CODO ERGO SUM |
 |
|
|
|
|
|
|
|
Best Practices in DB Design