Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

Re: Storing multiple value field in a MySQL database

by roboticus (Chancellor)
on Nov 25, 2013 at 20:05 UTC ( #1064286=note: print w/replies, xml ) Need Help??

in reply to Storing multiple value field in a MySQL database


If you have to store it in a single column, you can turn the list into a string with a suitable delimeter, such as:

my $members = join("; ", @member);

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:

001 Bennet Tony 001
002 Sinatra Frank 001 002
003 Martin Dean 001 002 003
004 Davis Sammy 001 002 003
005 Lewis Jerry 002 003
006 Jones Spike 004
007 Hope Bob 001 002 003
001 Singer
002 Actor
003 Comedian
004 Musician

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:

select * from celebrities where professions like '% 002 %'

Hmmm ... that's not quite right[1], 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:

select * from celebrities where professions='002' -- The only item in the list or professions like '002 %' -- it might be first of several or professions like '% 002' -- it could be the last of several or professions like '% 002 %' -- or in the middle

The code to add the acting profession to a celebrity is a little trickier[2]. (We'll use X to hold the ID of the celebrity we're interested in):

if 1 > (select count(*) from celebrities where (professions='002' or professions like '002 %' or professions like '% 002' or professions like '% 002 +%') and cel_id=X ) then /* Celebrity isn't an actor, so add to the end */ update celebrities set professions = '002' || case when professions is null then '' else ' '||professions end where cel_id=X; end if;

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:

001 001
002 001
002 002
003 001
003 002
003 003
004 001
004 002
004 003
005 002
005 003
006 004
007 001
007 002
007 003

Finding all celebrities who are actors is much simpler:

select * from celebrities where cel_id in ( select cel_id from celebrity_professions where prof_id='002' )

Adding the acting profession is similarly easy:The code to add the acting profession to a celebrity is a little trickier[1]:

if 1 > (select count(*) from celebrity_professions where prof_id='002' and cel_id=X ) then insert into celebrity_professions (cel_id, prof_id) values (X, '002 +'); end if;

Deleting the acting profession for celebrity X is even simpler--We just delete the association, without regard to whether it exists or not:

delete celebrity_professions where cel_id=X and prof_id='002';

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:

select * from celebrities where cel_id in ( select cel_id from celebrity_groups where group_id in ( select prof_id from celebrity_groups where cel_id=X ) )

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.


[1] 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.

[2] 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.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1064286]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (5)
As of 2018-07-21 00:03 GMT
Find Nodes?
    Voting Booth?
    It has been suggested to rename Perl 6 in order to boost its marketing potential. Which name would you prefer?

    Results (442 votes). Check out past polls.