Storing variable amounts of data

[Strahan]

Hi.  I have a database that catalogs my TV shows.  I added the ability to mark various shows as being related to each other, like Cheers is linked to Frasier because of the shared character(s).  I have a table for series groups with an auto inc int field for the index and a groupname text field.

 

My problem comes with the group member list.  It could be any amount of fields, from 2+ depending on how many shows are grouped together.  My original plan was ten int fields "member1", "member2", etc etc but what if for some reason I want >10?  My next thought was a text field "members" where I have a list of ints (the ints are the show info table index value) comma delimited.  Problem with that is, if I have these records:

 

gid = 1

members = "506;12;279;973;"

gid = 2

members = "2318;28;1506;500;"

 

Then when I want to do a lookup to see if show 506 is a member of a group I'd do "select groupname from seriesgrp where members like '%506;%'" then I'd get a match on both when it is not really correct to match on 2.

 

What's the proper way to handle something like that?  Thanks!

[jl5501]

The correct way to handle a situation like this, is to have a separate table for the members.

 

You would then have your master table with its index field, and any other data you put in there, and a members table with its own index, and an index field containing the id of the relevant record in the master table.

 

That way you can have as many members entries as you like, for each master entry, each with its own record. Output would then be a simple matter of an sql join.

[Strahan]

*smacking myself*

 

I can't believe I didn't think of that.  Man I feel like an idiot newb   Thanks!