faqts : Computers : Databases : MySQL

+ Search
Add Entry AlertManage Folder Edit Entry Add page to http://del.icio.us/
Did You Find This Entry Useful?

18 of 24 people (75%) answered Yes
Recently 8 of 10 people (80%) answered Yes

Entry

How do I store a variable number of values in a field? Let's say I have "clubs" and between 1 and 500 unique member ID's per "club"?

Feb 12th, 2008 04:23
dman, Mister Fribble, Narendra Jain, Andre Andreev, http://sturly.com


Whenever there are many entries per field, there is no direct way to
handle the situation. These are best handled if the field is defined as
a table within the database. Then you may have as many entries in this
table as you want. You may even control whether the entry is active or
not.
So, in this case of clubs, I would have a table called club as follows:
create table club 
  (member_id integer,  
   member_name char(30),
   active_flg char(1)
   start_date date,
   expiry_date date
  );
Now, in this table I could store as many Member informations as I want.
And even control if the member is active or not, without actually
deleting the perosn's information from the database.
You could also keep Clubs and Members in separate "Master" tables 
using "Club_Id" and "Member_ID" as their respective primary keys.  
Then create a relational domain table called "Membership" that 
maintains the Club / Member relationships.  The Membership table would 
use "Club_ID" and "Member_ID" as a primary key with foriegn key 
constraints back to the appropriate table.