mySQL - Adding multiple Item ids from inventory table to order table

Question

Im working on a project to learn database and client design and I'm a bit stuck. My order table consists of:

- order_id(int(11)) - This is the primary key and is auto incrementing.
- customer_id(int(11)) - A foreign key to the customer table 
- bike_id(int(11)) - A foreign key to the bikes table 
- equipment_id(int(11)) - A foreign key to the equipment table.
- date_from(datetime)  
- date_to(datetime)

What im trying to do is allowing a customer to have multiple bike_ids inside the order_id separated by a comma like so "6, 8, 10" - same goes for equipment_ids

I suspect it might be the datatype for bike_id which is an int and that it does not allow anything other than a single whole number.

Im starting to feel like this might be poor database design, but I dont see any other way of doing this at the moment. This is also going to be one of the main features of the system - so I want to get it right. I have no problem reading documentation, so perhaps someone can give me some pointers to how this kind of problem should be solved?

dont store data `comma separated` instead insert a separate row for single `id` — Ameya Deshpande, Mar 18 '15 at 12:47
If i understand you correctly i would then structure it like so: order_id, customer_id, bike_id, bike_id, bike_id ... ? — Nopes, Mar 18 '15 at 12:53
No, in rows, not in columns. Read about normalization: http://en.wikipedia.org/wiki/Database_normalization — fancyPants, Mar 18 '15 at 12:56

score 2 · Accepted Answer · edited May 23 '17 at 12:22

As Yngve Molnes asked in comment I will explain for you.

Store data data comma separated is bad practice. You should store bike_id like that:

order_id customer_id bike_id equipment_id date_from date_to
   1          1         1         1         2015      2016
   1          1         2         1         2015      2016
   1          1         3         1         2015      2016
   2          1         7         1         2014      2015
   2          1         4         1         2014      2015

You can read documentation about database normalization.

Comma-separated lists have a lot practical problems:

Can’t ensure that each value is the right data type: no way to prevent 1,2,3,banana,5
Can’t use foreign key constraints to link values to a lookup table; no way to enforce referential integrity.
Can’t enforce uniqueness: no way to prevent 1,2,3,3,3,5
Can’t delete a value from the list without fetching the whole list.
Can't store a list longer than what fits in the string column.
Hard to search for all entities with a given value in the list; you have to use an inefficient table-scan. May have to resort to regular expressions, for example in MySQL: idlist REGEXP '[[:<:]]2[[:>:]]'
Hard to count elements in the list, or do other aggregate queries.
Hard to join the values to the lookup table they reference.
Hard to fetch the list in sorted order.
Storing integers as strings takes about twice as much space as storing binary integers. Not to mention the space taken by the comma characters.

More about Comma-Separated lists

I accepted this as an answer, because it got me on the right track. Thank you for being so informative! — Nopes, Mar 18 '15 at 16:38

mySQL - Adding multiple Item ids from inventory table to order table

1 Answers1