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 ?
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.
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.
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');
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?
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.
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.
tblJokes
fldJokeId
fldUserId
fldVotes
tblVotesCast
fldVoteId
fldJokeId
fldUserId
When the user clicks the “like” button:
check in tblVotesCast if fldUserId already exists for the fldJokeId associated with the “like” button
if it does, tell the user they have already voted for this joke.
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?
hehe