Documents Database

Hello,

I need to create a database to hold documents information.

1. Basically, I need the following information for each document:

Title, Description, LastUpdated, Category, Type, Url

Should I create tables for Category and Type?
And link them to my documents table?
What type of relationship should I use?

2. I also need to know how many downloads each document had

Should I add a column in my documents table?
Then I would increase it one by one.
Or should I create a new table which would register each download.

3. I need to let users to rate each document from 1 to 5.
How should I implement this?

Thank You Very Much,
Miguel

Hey,

1. You could; some like to use unnormal form to do it, whereas some would break it out into a one-to-many relationship. The choice is up to you.
2. You could store it in the same table, and create a stored procedure that just handled the increment. You could even index that field, which may or may not make it more efficient.
3. I've seen it done where in that table, you store the total number of times something was rated, and the total rating given, then perform the math to get the value. You can store that in the same table, and give it its own update stored procedure to perform that.

|||

1. Yes you should make seperate tables for them and store a foreign key to the category and type in your document table.

2. I would do a seperate table to store the download count. You could then store some extended information for more stats, like datetime fill was downloaded, ip, etc... Doing inserts into a new table would just be a cleaner solution in my opinion.

3. Store the ratings in a different table as well. This way you can control user ratings by user and you can get averages etc.

|||

Hey,

It depends what you want to store. Do you want to store just the total value, or an entry for each download? Do you want to store total ratings, or rating per user so you can lock them out of rating something again? If you just want to store the total, I would store these values in the existing database. If you want the additional information like posted above, then use different tables.

You have to keep in mind performance as well; if you have to calculate lots of values, this may be an added overhead.