I want to know how to make a Reviewing Database.

[filmpotato]

I have seen a lot of website that have reviews on products and other things like movies, tv shows, books. I want to know how to do that. Can anybody help me with that?

[SheSaidImaPregy]

The site or the database? The database is actually quite easy:

ReviewInfo
- ReviewID
- TypeID
- Category
- DateCreated
- SubmitterID

Reviews
- ReviewID
- Review

This sets you up for anything you're about to encounter. Let's discuss:

ReviewInfo
- ReviewID (The ID of the review)
- Category (Or Product: Books, Television, Shows, etc.)
- TypeID (ID of the Type of Product)
- DateCreated (The date the review was created!)
- SubmitterID (The ID of the user who submitted the review)

Reviews
- ReviewID (Related to the info)
- Review (The actual review!)

Okay, for each review you want to single it out and that way you give it a unique ID. That ID says that the review is unique and shares no relation with any other records in that current table. However, this leaves open the possibility of relations to other tables! (Like Reviews)

Category: I chose this because you do not want to have 20 columns for ID's (bookid, showid, etc.). This way you can specify where the ID is located. By having the Category Books, you know that the corresponding ID is for a Book. Then, with programming, you can code in on where to look based on the current products being displayed.

The TypeID saves space as this is where you store the ID of the product you are reviewing and you only need one column. Not a TVID column, a BookID column, etc. It generalizes it to share the information.

Everyone wants to know when the review was created. Have you ever seen a review without it?? I don't know about you, but I get very frustrated when looking up reviews for products or entertainment just to find information about it that is six years old. Well... not very reliable information is it! Even the ghetto changes in six years.

Of course you want to know who submitted the review. And it is most likely a good idea to have a person register before submitting a review. This way if someone finds a review they found helpful, they can see what else the user reviewed!

Now the ReviewID in both tables just relate the data. They tell each other that the data in table ReviewInfo corresponds to the data in table Reviews. This way when you look up the review, you receive the correct review and the information about it.

The reason why Reviews has its own table is because that table can be very large, and become that way very quickly. A text column takes up one byte for every character, and there are over 56 thousand characters allowed per Text column. If you need more, then move it to BigText or the alike, where it allows about two million characters! But if each reviewer left a review containing 20,000 characters, then that's 20kb per review. That may not seem like a lot, but when you have 2,000 reviews, that's a whopping 40 mb. Now that isn't very much, at all when most companies allow you to have Gigs worth of space (Thousands of MB), however, if you have even 1,000 views on that 40mb, you are now talking about 40,000mb (40 gigabytes) worth of Transfer bandwidth, on review text alone! Then just imagine that an average page on your site with graphics and text can take more than 60kb to download. Combined, that's 80kb per review. So now with 1,000 page views, you are looking at 80 gigabytes of transfer for only 1,000 page views on your site. That's not that much at all, as even unadvertised private sites receive more than that on a monthly basis.

[filmpotato]

wow, now thats what i call information. thanks so much. but how would i incorporate that into a website. I am very good at learning new things, i am a newbie though, but i understand easily. Right now i only have a basic yahoo domain. I am planning on upgrading to the store when i find the right distributor. But i want to have a client base before i do that (Hence the reviews). My site will be about movies, from movies that are coming soon to old DVD. I know this has been done, and there are many sites to compete with, but for now its just a hobby.

[SheSaidImaPregy]

Who cares about competition? There's no need to worry about competition unless you need to be #1 in the industry. Otherwise being number 74 still has income and more than enough to live off of :)

To incorporate it into the site, that's easy as well. However, since I don't know how your site is built, it is not something I can comment on.

You just tie the TypeID to the MovieID in your Movies database. Then once tied, you will be able to relate the data. Some kind of SQL like this would work:

SELECT Review FROM Reviews WHERE ReviewID=(SELECT ReviewID FROM ReviewInfo WHERE TypeID=@TypeID)

'@TypeID is the querystring ID that you used, like:
'www.mysite.com/Movies/movies.aspx?id=235123

This is not an easy language, but it is something I believe will be very useful in your future:
ASP.NET

The reason why it is not easy is that it is based off other languages. ASP.NET is just like another version of HTML, but is controlled by a backend language like VisualBasic.NET (VB.NET) or C-Sharp (C#).

But what is great about this language is that the tools for it are amazing. You can create extremely rich applications very quickly and enabled with AJAX and animations. Look it up, it's becoming one the most popular languages and very quickly.

Another great thing about ASP.NET is that it has Membership, which allows you to create a very indepth user system where each user has a role that they can play (certain rights). And things are much easier and less problem prone.