Page 1 of 1

db design - saving lists

Posted: Tue Mar 13, 2007 9:24 pm
by Luke
I have a system with three tables: shows, schedule, and channel. Shows are inserted into the schedule and assigned to a channel, so the relevant columns are something like:

shows:
id
name
channel_id

channels
id
name

schedule
id
show_id
start_time
end_time

Now, the user is able to build (via javascript) a block of shows and either insert it into the schedule, or insert it into the schedule at a later time. How would you recommend saving these lists? They need to be named (ie: "Sunday Line-up"). I was thinking of something like:

lists
show_id
list_name (this would be the primary key)
order

but that to me just feels wrong for some reason. What do you think?

Posted: Wed Mar 14, 2007 1:16 am
by Kieran Huggins
seems fine to me...

Posted: Wed Mar 14, 2007 3:52 am
by mikeq
list_name as the primary key, can you ensure this is unique?

Posted: Wed Mar 14, 2007 10:23 am
by Luke
I made it the primary key... that ensures it will be unique all by itself doesn't it??

I mean the user can enter in the same name twice, but it's not going to let them save it. I'll make sure to tell them why it didn't work and let them rename it if it fails.

Posted: Wed Mar 14, 2007 12:25 pm
by mikeq
I made it the primary key... that ensures it will be unique all by itself doesn't it??
Not quite what I meant. In the real world should the list name be unique?

So if one user comes along and names their list "Sunday Line-up", then I come along, create a list and name it "Sunday Line-up" I'll get an error because someone else used up that name.

So maybe the primary key should be made up of

userid, list_name (so you would need a userid field in your lists table)

That way each users list names must be unique but it doesn't stop users creating a list name the same as some other users list name.

Just a thought.

Posted: Wed Mar 14, 2007 12:42 pm
by Luke
No actually the names should all be unique because all users will be using eachother's lists, but good point.