Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

Comment on

( #3333=superdoc: print w/ replies, xml ) Need Help??

mba777:

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:

Celebrities
Cell_IDLast_NameFirst_NameProfessions
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
Professions
Prof_IDProfession
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:

Celebrity_Professions
Cel_IDProf_ID
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.

Notes:

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

...roboticus

When your only tool is a hammer, all problems look like your thumb.


In reply to Re: Storing multiple value field in a MySQL database by roboticus
in thread Storing multiple value field in a MySQL database by mba777

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • Outside of code tags, you may need to use entities for some characters:
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others having an uproarious good time at the Monastery: (4)
    As of 2014-09-21 04:24 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      How do you remember the number of days in each month?











      Results (166 votes), past polls