db design - saving lists

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

db design - saving lists

Post 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?
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

seems fine to me...
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

list_name as the primary key, can you ensure this is unique?
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

No actually the names should all be unique because all users will be using eachother's lists, but good point.
Post Reply