Better design?

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
thatsme
Forum Commoner
Posts: 87
Joined: Sat Apr 07, 2007 2:18 am

Better design?

Post 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
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Better design?

Post 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?
(#10850)
thatsme
Forum Commoner
Posts: 87
Joined: Sat Apr 07, 2007 2:18 am

Re: Better design?

Post 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)?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Better design?

Post 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
(#10850)
Post Reply