|Problems? Is your data what you think it is?|
Re: Storing multiple value field in a MySQL databaseby roboticus (Chancellor)
|on Nov 25, 2013 at 20:05 UTC||Need Help??|
If you have to store it in a single column, you can turn the list into a string with a suitable delimeter, such as:
That can do the job, but it really limits the utility of the member list information in your database. It's rather difficult to ask the database to give you the groups in common between two people, the list of people in any particular group, etc. As indicated by davido, storing the information in more than one table will let you ask more interesting questions about the data.
For example, suppose you have these tables:
The Professions column holds a list of the professions the celebrity is a member of. Unfortunately, this column isn't very easy to use. Sure, you can query it to find a list of all celebrities who are actors:
Hmmm ... that's not quite right, if the actor group ID is first or last, it won't have a blank on either side, so you'll *really* need something more like:
The code to add the acting profession to a celebrity is a little trickier. (We'll use X to hold the ID of the celebrity we're interested in):
More complicated operations get messier very quickly. Imagine how to ask SQL to delete the acting profession from a celebrity.
Let's add a "many to many" table to join celebrities and professions. It makes the data a little more complex, but the code much simpler:
Finding all celebrities who are actors is much simpler:
Adding the acting profession is similarly easy:The code to add the acting profession to a celebrity is a little trickier:
Deleting the acting profession for celebrity X is even simpler--We just delete the association, without regard to whether it exists or not:
Now a more complicated question: Given a celebrity, give a list of all celebrities they may have worked on a project with. I wouldn't attempt to do that with the original table layout, but with the current layout, it's pretty simple:
So the innermost select gives us a list of all the groups the celebrity is in. The next query out gives a list of all celebrity IDs in all of the groups we just retrieved. The outermost query simply gets the information we want from the celebrities table for all the celebrity IDs we have.
 If we ensure that we use a non-digit delimiter and all the profession IDs are exactly the same length, we could get away with '%002%', but I want to stress how ugly a list in a string actually is. Even with the fixed length group ID, deletion and insertion are still ugly.
 The SQL syntax used for the conditionals isn't standard SQL. (In fact, I'm not sure there *is* a standard syntax for things like that.) But each of the dialects of SQL I've used so far give you a similar construction.
When your only tool is a hammer, all problems look like your thumb.