table design

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
SidewinderX
Forum Contributor
Posts: 407
Joined: Fri Jul 16, 2004 9:04 pm
Location: NY

table design

Post by SidewinderX »

Hello, I've made a `Top Sites` script and in version 1.1 I'm going to be implementing "Rate this site" and "Comment this site" functions. The user will have the ability to either vote and comment, just vote, or just comment.

The table is obviously going to have the id column which corresponds to the site that is being voted/commented on, but I was wondering, should there be two separate tables [one for the votes and one for the comments] or should I just use one table, and if the user only votes, insert nothing into the comments column and vice-versa? Or is there a completely different design I am missing?
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: table design

Post by superdezign »

SidewinderX wrote:Hello, I've made a `Top Sites` script and in version 1.1 I'm going to be implementing "Rate this site" and "Comment this site" functions. The user will have the ability to either vote and comment, just vote, or just comment.

The table is obviously going to have the id column which corresponds to the site that is being voted/commented on, but I was wondering, should there be two separate tables [one for the votes and one for the comments] or should I just use one table, and if the user only votes, insert nothing into the comments column and vice-versa? Or is there a completely different design I am missing?
You could separate them, but you can easily cope for having them merged with NULL columns.
SidewinderX
Forum Contributor
Posts: 407
Joined: Fri Jul 16, 2004 9:04 pm
Location: NY

Post by SidewinderX »

Thanks, but I know that :P My question was, which is better?
User avatar
boo
Forum Commoner
Posts: 42
Joined: Mon Jul 02, 2007 11:30 am
Location: NY

Post by boo »

SidewinderX wrote:Thanks, but I know that :P My question was, which is better?
I would have to say the same table would be better that way you dont have to join to another table to see the comments for each vote that they entered both for.
SidewinderX
Forum Contributor
Posts: 407
Joined: Fri Jul 16, 2004 9:04 pm
Location: NY

Post by SidewinderX »

Thank you, does anyone else have any input?
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post by nathanr »

Code: Select all

Sites
SiteId | (more columns)

Users
UserId | (more columns) | VotePermission | CommentPermission | (more columns)

Votes
VoteId | SiteId | UserId | Vote | (other needed columns)

Comments
CommentId | SiteId | UserId | Comment | (other needed columns)
This would be my preference; Reasons: you should always think "if i want to expand the voting system, and the comments system at stages 1.2> then will a single table handle it..?"

Additionally, adding in userId and SiteId at this level also lets you constrain at db level that nobody can vote on a single site more than once or indeed comment on a single site more than once. (by adding a unique index over SiteId|UserId)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

Oh, OK, you got me hooked, again. The determination of what tables to create and what columns within each table is not a casual, "which do think is better?" issue. The rules are well defined and objective and the success of any relational database design is dependent on doing it right. A database is a model of a real-world situation. Each table represents an entity (person, object, event, transaction, etc.). Each column is a property of the entity represented by the table. Think in terms of entities and properties. You should first identify what entities in the real world your database is intended to model. If you can't clearly define every entity, you're not ready to start creating tables.

Once you have a solid schema, based on entities and properties, you can then begin to apply the rules of data normalization. If you don't know what these are, you should look it up in Google and study it before doing anything else.
SidewinderX
Forum Contributor
Posts: 407
Joined: Fri Jul 16, 2004 9:04 pm
Location: NY

Post by SidewinderX »

califdon wrote:Oh, OK, you got me hooked, again. The determination of what tables to create and what columns within each table is not a casual, "which do think is better?" issue. The rules are well defined and objective and the success of any relational database design is dependent on doing it right. A database is a model of a real-world situation. Each table represents an entity (person, object, event, transaction, etc.). Each column is a property of the entity represented by the table. Think in terms of entities and properties. You should first identify what entities in the real world your database is intended to model. If you can't clearly define every entity, you're not ready to start creating tables.

Once you have a solid schema, based on entities and properties, you can then begin to apply the rules of data normalization. If you don't know what these are, you should look it up in Google and study it before doing anything else.
Excellent explanation, I've always looked at a database as a place to store `stuff` not as a means to model a real world entity. CS-115 all over again :)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

SidewinderX wrote:Excellent explanation, I've always looked at a database as a place to store `stuff` not as a means to model a real world entity. CS-115 all over again :)
Well, I taught courses like that for nearly ten years. :) If you're planning to do a lot of database design, do yourself a favor and spend a couple of hours reading Chris Date's historic book(s) on Relational Databases. Have fun!
Post Reply