Best way of storage delimted values in MySQL

I have an array that stores an arbitrary set of items for a defined batch. Each item is defined in a separate table with its own properties and values. So say, for example, batch with id 1 contains items 1,3,11,16,17, and batch 2 contains items 5,11,15. What is the proper way of storing arrays like that in MySQL? Currently, I'm storing the items as comma delimited strings in an items column. Whenever I need to retrieve the info, I query that column, split the string into an array, loop through that array, create a new query form the split values, and then run a new query joining in the split values. Surely there must be a better way of doing this?

Example of the table layout:

Batches table:
==============
id|items
--------
1 |1,3,11,16,17
2 |5,11,15

Items table:
============
id|description     |serial
--------------------------
1 |some description|00046552
2 |etc             |00046653
3...

Answers


I would store the data as a many-to-many relationship:

Batches table:
==============
id | Batch description
---+------------------
 1 | Some description
 2 | Some other desc...


Items table:
============
id|description     |serial
--+----------------+------
1 |some description|00046552
2 |etc             |00046653
3...

Batch items:
============
batch_id | item_id
---------+--------
 1       |  1
 1       |  3
 1       | 11
 1       | 16
 1       | 17
 2       |  5
 2       | 11
 2       | 15

If the order of items in a batch is important, you can add another column to Batch items table to indicate an order within a batch; or simply use item id as the ordering column.

This way you can you can enforce referential integrity using foreign keys and make it so much easier to manipulate individual items within a batch.


It's a bad design to store comma separated value in a column. Normalize the database properly. Convert the table into 2-table design if the relationship of Items to Batches is One-to-many.

Example,

Items

  • ItemID (PK)
  • Description
  • Serial

Batches

  • BatchID
  • ItemID (FK)

But if you have Many-to-many relationship, consider adding new table that links between the two.

Items

  • ItemID (PK)
  • Description
  • Serial

Batches

  • BatchID (PK)
  • otherColumns...

Batch_Item

  • BatchID (FK) -- unique pair with ItemID
  • ItemID (FK)

The 'SQL correct' method would be to have multiple rows per id and use a combined PRIMARY KEY of id and item like:

id| item
--------
1 | 1
1 | 3
1 | 11
...
2 | 15

I'm not sure if this would increase performance overall. Either way you're going to be looping an array, you cut out the step of turning a delimited list into an array but you add more database rows and more results to the SELECT statement.


Need Your Help

How does one replace brand text with an image on twitter bootstrap nav bar?

html css twitter-bootstrap

I've tried to replace the brand text with an image as you can see in my example index page. But, this causes the nav to break into two lines with the logo image on its own line. How can i fix thi...

run script to generate new image size from existing images using imagemagick

ruby-on-rails imagemagick carrierwave

I am using CarrierWave with ImageMagick for my Rails application. I'd like to run a script to generate a new image version of all existing images. What would be the best way to do this? I have not

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.