mysql logic - suggestions?

[cgraz]

I've surprisingly never worked on very complex projects involving multiple relationships between tables, but I've been meaning to for a long time. I'm developing a personal blog system to use on my site in order to get some practice. I came up with the following table schemes, and was wondering if someone could please help me sort it out for logic or similar suggestions.

ARTICLES
art_id (primary)
art_title
art_summary
art_body
art_commentsA (on/off)
art_commentsB (open/closed)
art_ratingsA (on/off)
art_ratingsB (open/closed)
art_timestamp

All comments in this table linked to the article the comment was posted using art_id

COMMENTS
comment_id (primary)
art_id
comm_name
comm_url
comm_email
comm_body
comm_ip
comm_timestamp

Listing of all the available categories

CATEGORIES
cat_id (primary)
cat_title

An article can be posted in multiple, categories, so this table will link it to the article by art_id and the category that article was posted in using cat_id

POSTED_IN
posted_id (primary)
art_id
cat_id

Visitors should also be able to rate my articles on a 1-5 scale. I was originally thinking this table will hold all the votes, then sum them up and divide by the total everytime it's queried. Should I just keep the calculated rating as one entry, rather than having all the ratings then calculating? So for example:

One article might get rated 4, 3.5, 5, and 2. This averages a 3.625 rating, which is what I would display on my site. Should I store all four entries, then calculate the average, or should I just store 3.625 and update the rating as more posts are added?

RANKINGS
rank_id (primary)
art_id
rank_score
ip

Thanks.

[bbreslauer]

Most of that looks fine, though I"m not quite sure what the art_commentsA, art_commentsB, art_ratingsA, and art_ratingsB are for. The rankings part you've got an issue with, though. How you're currently thinking about doing it won't work, because you won't know how many people will have already rated it. Take the following example:

1) you have 2 ratings, 1 and 5. Therefore, (1+5)/2 = 3, which would be the average. Then you get a third rating, 5. (3+5)/2 = 4, which is your new average.

2) you have 3 ratings, 1, 5, and 5. (1+5+5)/3 = 11/3 != 4.

So you've got an issue with the logic there. If you want to be able to track visitors that rate your articles (for example, to prevent duplicate entries from the same IP), then you'll need to hold every vote, with a table like you have. If you only want to keep track of the total, then you can have two columns in your article table, one for the raw ranking, which is just the sum of all the rankings for the article, and the other being the number of people who have voted.

[cgraz]

Thanks for your help. I ended up changing all of the primary keys in each table to just id, but left the references to those id's as is in other tables. So in articles, it's just id. In posted in, it refers to that as art_id.

I've been reading up and playing around with joins, but I was wondering if there was an easy way to do the following without multiple queries for each category.

Basically what I'm trying to do is grab each category, then find the articles within that category along with their id's to create a listing that looks like this

CATEGORY 1
Article A
Article B
Aricle C

CATEGORY 2
No Articles

CATEGORY 3
Article A

And so forth. Here's my (messy) code.

// Pull all fields from Database
$query = mysql_query("SELECT * FROM categories ORDER BY cat_title");



	<? while($row = mysql_fetch_assoc($query)) { ?>

	<b><?= $row["cat_title"] ?></b><br />

		<blockquote>

		<? $query2 = mysql_query("SELECT articles.art_title, articles.id FROM posted_in,articles WHERE posted_in.art_id = articles.id  AND posted_in.cat_id = '" . $row["id"] . "'");

			if(mysql_num_rows($query2) == 0) {

				echo "No Articles";

			} else {

				while($row2 = mysql_fetch_assoc($query2)) { ?>

					<a href="#"><?= $row2["art_title"] ?></a>

				<? }

			} ?>

		</blockquote>


	<? } ?>

Thanks.

[bbreslauer]

Make sure that you alias your id fields if you do a query with two tables, or else bad things could happen.

The logic that you're using for the categories seems to be what I'd do; pull out all the categories, then run through a loop, getting articles from that. You could do a join on two tables, but then you'd get into some annoying parsing, because you'd have to deal with when you finished with one category.