Database Driven Site

Hi Guys

I am new to PHP & MySQL.

I want to learn by diving into a “Small Project” (I’m calling it small, because it sounds very simple, but we shall see). At University I have a Module called { Relational Databases & Web Design }. The Lecturer said:

Whenever you want to start a Database Driven website, it is always best to start with a Database UML Diagram to get things nice & clear.

The “small project” I have in mind is a common one for PHP beginners. It is a Jokes Website.

On the website, visitors will be able to:

  1. View Jokes
  2. Register to
  3. Add Jokes
  4. Report Jokes
  5. Contact Admin

I have thought about the database design and I came up with the following:

Before I start my project, I want your opinions on the database design, and also to see whether I have missed some important stuff.

Thank You.

Looks like you still need a way to differentiate admins from normal users. Perhaps just adding a column to the users table called admin 1 or 0 is the answer to that.

The other thing is how were you planning on having jokes reported? Your schema doesn’t seem to account for that so I was thinking it would just be an email to every admin or something?

Thanks Guido.

So is something like this OK ?

Thank You

That depends. You’d need a rating table, with columns for the joke id, the rating, and the id of who voted. That could be the user id, if only users can vote. Or the IP if anyone can vote, and you don’t want double votes. Or if you don’t care who votes and how many times, the rating table could do without that third column.
The primary key would be userid/jokeid, or ipnumber/jokeid, or none in the last scenario.

I don’t know :slight_smile:
I was just making sure that you thought about it, since your ERD is set up to have one category per joke. If that is the way you intended it, than there’s no problem. If it wasn’t, and you’d find out half way, or at the end, it could be a heck of a job changing everything (and it is something every now and again comes up in forum posts ;)).

I’d say it looks good to me.

Erm Yeah ! lol

I couldn’t think of a joke that would have two categories.

Can you give an example ?

A joke can have only one category?

Hi Oddz

Thank You, I get the logic behind your first point. But I’m not sure about the second.

To report, I’ll have a little Report button at the right hand corner of each joke. When a user clicks that, he gets a form, and he is able to write why he has reported the joke.

Then when he clicks the final Report Button (on form page) I’ll get an email with the JokeID, then I can take a look.

Can you please explain what you had in mind.

Thank You.

Cool Thanks Guido :slight_smile:

One more thing, say I wanted to have a rating feature !

I want people to rate jokes from 1 star to 5 stars. What do I need to change in the Database design ?

Thank You.

Should rating have a jokeID as a foreign key?

nice catch :slight_smile: