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_ID | Last_Name | First_Name | Professions |
|---|
| 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_ID | Profession |
|---|
| 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_ID | Prof_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. |