1

I have 2 tables Product that contains products of the shop and ProductStatus that indicates if product is new, sold, etc. One product may have many statuses.

I need to link Product and Status. I thought about two ways:

  1. add 3rd table ProductToStatus that will match ProductId with ProductStatusId
  2. add Status column to Product table that will contain the IDs of the statuses separated with commas("4,12,34,");

What the pros and cons of each solution above or maybe there is another common way to do this?

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
theateist
  • 13,879
  • 17
  • 69
  • 109
  • 1
    Never never never, at all, even on sundays, bank-holidays, or after drunken nights of passion or romance, ever, please, for the sake of little bobby-tables, use strings of comma separated values in database fields :) – MatBailie Dec 01 '11 at 11:52

5 Answers5

5

First way is correct, and more common. A table used to model many-to-many relationship is called a join table, relationship table or junction table, among other names.

Its main adventages over second way you propose are:

  • faster database operations - for example, if you want to find all the products with certain status, the query using join tables will be pretty fast with right indexes created, while the second way you propose would mean searching for a given substring in every product (which would be slow).
  • constraints checking - you can declare ProductId andProductStatusId columns as foreign keys, and the database will stop you from assigning a non-existant status for a product.

Main disadventage of a join table is that the code for adding / modifying Products can be a little harder to write, as you need to manipulate more database records. Fortunatelly, modern ORM frameworks take care of all that work behind the scenes.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
socha23
  • 10,171
  • 2
  • 28
  • 25
  • "Fortunatelly, modern ORM frameworks take care of all that work behind the scenes" ...but [do they play nice with the compound keys](http://stackoverflow.com/questions/218100/orm-support-for-compound-primary-keys) which naturally occur in relationship tables? – onedaywhen Dec 02 '11 at 11:29
  • @onedaywhen Hibernate and GORM (which is based on Hibernate) create proper compound keys for join tables. – socha23 Dec 02 '11 at 12:01
3

Option 1 is by far the better and most commonly used as you can setup your link table (ProductToStatus) with foreign keys to both tables which will maintain data integrity.

justcompile
  • 3,362
  • 1
  • 29
  • 37
2

The 2nd approach sounds rather clumsy. How you are going to perform queries over such model? The 1st approach is correct, this is how people usually model it.

Wiktor Zychla
  • 47,367
  • 6
  • 74
  • 106
1

For the sake of the model, alternative 1 should be used. Alternative 2 is an ugly workaround.

You couldn't even make an FK if you use alternative 2.

everton
  • 7,579
  • 2
  • 29
  • 42
0

The problem with option 2 is that your new data structure is not compatible with SQL's operators. For example, the value "4,12,34," should be considered the same as "4,34,12,": in absence of being able to overload SQL's operators, you'd need to a) write user defined functions and b) train users to use the UDFs rather than SQL operators. Repeat for every operator.

The same applies to constraints. For example, you would need to prevent the value "4,12,4," because it contains duplicate members. Again, you'd need to roll your own constraints, presumably using SQL CHECK constraints.

In the process of writing these operators and constraints, you'd always need to split apart the members, operator on them, then concatenate them again. Which would beg the question: why not just keep them apart? Then we are back at option 1!

onedaywhen
  • 55,269
  • 12
  • 100
  • 138