View Full Version : best way of storing multiple items against a record and being searchable

02-11-2010, 11:06 AM
OK long title.

I'll try my best to explain how i have things setup at the moment and hopefully you guys can make some suggestions on the best way to do it since my head is not really working it seems today.

I have many companies each stored as a row in a table a
cid,lots of other columns

I have many currencies each stored as a row in table b
id, symbol, currencyname

Now what I want to do is assign 1-many currencies against each company.

So my first thought was have an additional column in table a and store a comma list of selected currencies in there. However this poses a couple of problems
1) I need to search on currencies traded by the company due to this column needing to be a varchar to store a csv I'd need to do a like % % search this would lead to mis matching as 1 would match 10 and 11 and 21 etc.
2) I also need to display a series of checkboxes on the company details page one for each currency available and have them checked if the company currently trades in it.

Option 2
I thought use a serialised array but then I read up on data serialisation and I can't search on it the way I need to so that was out.

Option 3
Have a new table table c which contains simply the company id and the currency id. so each entry in table a will have a one - many relationship with table c
not many problems with this idea as far as I can tell it'll work but makes data display very very slow as for displaying the checkboxes on the company details page i'd need to do a master select from table b and for each row do a select from table c where currency id= and companyid= to work out if i should be checking the boxes and again on updating the boxes its some funky checkbox magic again.

Are there any other options out there i'm missing or something far more simple?

02-11-2010, 12:33 PM
Option 3 is the right way to go about it. Using one of the first two will make things awkward

02-11-2010, 01:32 PM
figured as much i hate "messy" solutions was hoping there was an easier way i'd overlooked :(

02-11-2010, 02:10 PM
Not sure why you think it's messy to be honest, but it's the standard way of doing HABTM relationships

02-11-2010, 02:34 PM
Not sure why you think it's messy to be honest, but it's the standard way of doing HABTM relationships

Agreed, any many-to-many style table should include a flattening table between them. In this case, the company id and the currency id. This is part of you're normalization process and will (according to the info provided) style a 3NF database. There are cases when normalization is not feasible, but these are very rare (my brother for example works for a corp that requires 2NF on half of their tables due to the prohibitively expensive write speeds of the 3NF style).

02-11-2010, 02:46 PM
reading and writing speeds are one of my concerns which was why I was thinking of a way of doing it without a 3rd inbetween table I just couldn't think of a way around it though without having to do some insane array functions and some mad skillz javascript front end for array generation