table design
Moderator: General Moderators
-
SidewinderX
- Forum Contributor
- Posts: 407
- Joined: Fri Jul 16, 2004 9:04 pm
- Location: NY
table design
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?
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?
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
Re: table design
You could separate them, but you can easily cope for having them merged with NULL columns.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?
-
SidewinderX
- Forum Contributor
- Posts: 407
- Joined: Fri Jul 16, 2004 9:04 pm
- Location: NY
-
SidewinderX
- Forum Contributor
- Posts: 407
- Joined: Fri Jul 16, 2004 9:04 pm
- Location: NY
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)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)
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.
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
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 againcalifdon 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.
Well, I taught courses like that for nearly ten years.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