Database Design Confusion

Hi Guys

I’ve just finished the XHTML / CSS for a test project of mine (a jokes site). Now I’m planning on working on the server stuff. I thought before I start, I should review everything. Then I found a problem.

Here is the ER Diagram fo the Database.

What I’m confused about is the relationship between the ratings and the jokes. I only want a record to be created in the RATINGS table when someone clicks the { I like } button on the site. And obviously that record should somehow connect to a unique joke in the JOKES table.

Does my database design allow this ? if so can someone tell me what my MySQL INSERT query would look like ?

I would greatly appreciate any help.

Thank You.

The dotted line between the two tables indicates a 1 to 1 relationship, but in reality it’s a 1 to many: 1 joke can have many ratings, 1 rating can belong to only 1 joke.

Aha other than that, what is wrong with it ?

How do I get a row to be created dynamically ? At the moment I have the following SQL:

mysql_query('INSERT INTO ratings SET rating = rating++,
			ipaddress = $_SERVER['REMOTE_ADDR']');

Overall your db design look ok to me however I think you have misused some of the symbols on it.

I thought the circle means a record in that table is optional and the little vertical or horizontal bars means a record in that table is mandatory. On some tables, like USERS, you have both a circle and a vertical bar. I think you really mean that a jokeid must have an existing userid and so the USER table should only have a verical bar and not the circle attached to connector. The same applies to the RATINGS / JOKES connector. But that is beside the point of what you are asking.

Does my database design allow this ? if so can someone tell me what my MySQL INSERT query would look like ?

Yes it does.

You could use an AJAX function to send the jokeid associated with the button and the rating value selected by the user to a server side script to update the RATINGS table.

You could also include a userid column in RATINGS if only registered users can give ratings and you would like to keep track of ratings given by each user.

my confusion arises from the fact that I don’t really know how to write the sql query.

Can you tell me what my sql query would be for pressing the [ like ] button ? I doubt it that it would be this:

INSERT INTO ratings SET rating = rating++,
			ipaddress = $_SERVER['REMOTE_ADDR'] WHERE ratings.jokeid = jokes.jokeid

Thanks V. MUCH :frowning:

The structure of the sql query would be very similar to the structure of your sql queries to insert records in your USERS and CATEGORIES tables.

LOL Kalon. OK can you please check this and tell me if it is Right ?

while ($row = mysql_fetch_array($result)) {

	$jID_holder = $row['jokeid']; // This is a variable to hold the Joke ID (jokeid).
	echo $row['jbody'];


mysql_query('INSERT INTO ratings SET rating = rating++,
			ipaddress = $_SERVER['REMOTE_ADDR'],
			ratings.jokeid = $jID_holder');

Thank You

Without seeing all your code I can’t tell for sure if it is totally correct.

What happens when you run the code?

  1. did you get any error messages?

  2. did it do what you want?

If 1) = no and 2) = yes, then I would assume it is right.

Is the rating just the number of people that like the joke, or can each user give the joke a rating (on a scale from 1 to 10 for example) thus making the rating of the joke the average of all user ratings?

Good Question Scallio

The rating field is just an Integer field. Each time a visitor presses the { Like } button on a joke … the rating is increased by 1.

Then rating should be a field in the jokes table, not in the rating table.

You could put 1 in the rating table for every vote and then take the sum, but that doesn’t sound like it’s going to be fast now does it? :slight_smile:

So every time a user votes insert (jokeid,IP) in the rating table, and update the jokes table.

OMG you’re right !! LOL

OK so my Ratings table only needs jokeID and IPAddress ? no RateID or other fields needed ?

if you put the rating field in the jokes table you then might have to find another way to stop people from continually clicking the “like” button and so artificially bump up the rating value.

One option was suggested in post 4.

Hi Kalon

You’re right and that is what I think ScallioXTX was doing when he said:

So every time a user votes insert (jokeid,IP) in the rating table, and update the jokes table.

But I would like a bit more clarification on this.


ScallioXTX’s solution doesn’t eliminate the rating table, it just moves the rating column (which is a counter) to the jokes table. Advantage: you don’t have to do a sum of the ratings table each time you want to show the number of votes a joke has.
At the same time, the ratings table will contain all ips that have voted a certain joke, so checking for multiple voting from the same ip for the same joke is still possible.

ok, now that what you want is a bit clearer I would do something similar to this.





When the user clicks the “like” button:

  1. check in tblVotesCast if fldUserId already exists for the fldJokeId associated with the “like” button

  2. if it does, tell the user they have already voted for this joke.

  3. if it doesn’t, add a record in tblVotesCast for this user and fldJokeId and then increment fldVotes in tblJokes by 1 for fldJokesId associated with the “like” button.

You could also include IP addresses in tblVotesCast to check for repeat votes. I’m not sure how reliable IP addresses on their own is to stop repeat votes since some users’ IP address will change every time they connect to the www.

Also, what if more than 1 user on the same IP address wants to vote for a joke?

Thanks Guido and Kalon

OK Here is the site. Now you can see the Like Button { green plus }.

When a user clicks that, 4 things would happen as I currently understand it:

  1. Grab the ID of that particular Joke
  2. Get IP Address of the visitor clicking on the button
  3. Check if that IP Address has already Clicked for that Particular Joke ID
  4. If Yes (display some error message) if No > Increase the Rating of the Joke by 1 and add some message (e.g. Thank You)

Step 3 I am not too sure about, but how about this.

I keep the RATINGS table (as in the diagram) but this time I only have the following two fields:

{jokeid, ipaddress}

Then when a user clicks the button … I do a search of that RATINGS table to see if I find any matches … and go from there ?

Good … or No ?


That depends on the answer to what I asked earlier.

Also, what if more than 1 user on the same IP address wants to vote for a joke?

It would help if you posted what the “Business Rules” for voting were for your application.

I am assuming the Business Rules were finalised and agreed upon before you started designing your ER Diagram

Haha Sorry Kalon didn’t see your Question. The answer is:

Tough Luck I guess! :smiley: hehe

So that should work right ?