Page 1 of 1

CSV in a single MySQL field, is it a bad idea?

Posted: Tue Apr 22, 2003 12:13 pm
by mrbippy
Is it a bad idea to put CSV in a MySQL field?

Like: "A:B:C:D:E"

instead of using seperate fields for each value.

I ask because I have several items that are associated with sets of data. But the data sets are of variable lenghts from 4 to 13. If I set up a table with 14 fields, 1 for the item identifier and 13 additional fields I'll end up with a ton of NULL or blank fields.

Posted: Tue Apr 22, 2003 12:27 pm
by mchaggis
A ton of NULL's ain't gonna cause any overheads and is not a prob.

The other solution would be to normalise further and have a table that contains item id and value.

Code: Select all

1 => A
1 => B
1 => C
etc

Posted: Tue Apr 22, 2003 12:41 pm
by mrbippy
The values in the table are not supplied by users, so I have control over what gets entered.

That is kind of how the table is now.

sequence=>1, itemId=>1, pageVal=>"ABC"
sequence=>2, itemId=>1, pageVal=>"DEF"
sequence=>3, itemId=>1, pageVal=>"GHI"
sequence=>4, itemId=>1, pageVal=>"JKL"
sequence=>1, itemId=>2, pageVal=>"DEF"
sequence=>2, itemId=>2, pageVal=>"GHI"
sequence=>3, itemId=>2, pageVal=>"JKL"

etc.

But I have found it difficult to work with. I just added the sequence field so I could maintain the order of the pageVal when I queried the table.

I have 102 items each with variable number of pageVals. (4-13)
So this table has 845 records and many of them hold the same values in the sequence and itemId fields.

So I thought it might be easier to do something like this:

itemId=>1, pageVals=>"ABC:DEF:GHI:JKL"
itemId=>2, pageVals=>"DEF:GHI:JKL"

Then implode the $pageVal and use it just like any other array.

what do you think?

Posted: Tue Apr 22, 2003 2:22 pm
by McGruff
You can store multiple values in a single field separated by a string which does not occur anywhere in the data. The separator string can sometimes be a simple space or underscore or you might need something more obscure like |#|#|.

Anyway, to get the separate bits of data in an array:

$data_array = explode('string', $column);

Withough knowing more I don't know if that's the best solution - another option though.

Posted: Tue Apr 22, 2003 3:24 pm
by pootergeist
if you always know how many elements the array will contain then I'd advise developing a table structure to hold them seperately (or indeed a relational lookup table to reduce the actual data stored).

if you do not know how many elements are to be stored in each array then using a single text field is not a bad idea - though you will obviously incur a scripting overhead.

one point to note: developing scripts to remove one (or several) array indices can become quite complicated - worth thinking about before getting too far into the table building structure.