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:
View Jokes
Register to
Add Jokes
Report Jokes
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.
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?
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
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 ;)).
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.