Page 1 of 1

Better design?

Posted: Fri Jan 18, 2008 9:45 pm
by thatsme
I want to make a database where people login and insert jokes.

//Design 1

joke
joke_id
joke
category_id
author_id

category
category_id
category

author
author_id
author_name

// Design 2 - this is given in a php book

joke
joke_id
joke
author_id

category
category_id
category

author
author_id
author_name

jokecategory
joke_id
category_id

Thanks

Re: Better design?

Posted: Fri Jan 18, 2008 10:01 pm
by Christopher
The difference between the two is that in the first one each joke can be in only one category, in the second a joke can be in multiple categories. Which one do you want?

Re: Better design?

Posted: Fri Jan 18, 2008 11:38 pm
by thatsme
Thanks.

I was confused looking at the 2nd design. One more thing, so when the user adds in the 2nd design a seperate insert/update statement has to be written for inserting categories (i guess it should be checkboxes for multiple selection)?

Re: Better design?

Posted: Sat Jan 19, 2008 1:34 am
by Christopher
Yes, you would need to insert/update a record for each joke_category relationship. So:

joke (id, text)
1, Two peanuts were walking down the road, and one was assaulted peanut

category (id, name)
10, Jokes about peanuts
11, Jokes that kill

joke_category (id, jokeid, categoryid)
1, 1, 10
2, 1, 11