I'm making a review type website, and I would like for there to be 2 types of users. One would be the majority, reviewers while the other would be the person the review is about. How, in terms of database design would I differentiate the two. Should I have separate tables for reviewers and reviewies or simply assign a different range of ID's for both?
1
-
Thanks for the quick responses. The information will be all the same for both types of users. Some context, not the same thing but think ratemyprofessor.com, with the ability for the professor to respond to specific comments and reviews.CNJ– CNJ2010-01-26 04:48:12 +00:00Commented Jan 26, 2010 at 4:48
Add a comment
|
7 Answers
This is a classic super-type/sub-type situation. Users and Reviewers are both people, and relational bigots would say you should have one "people" table containing all of the common person fields. You can then have explicit one-to-one foriegn key relationship to a reviewers table, which contains the reviewer-specific fields and related keys to other reviewer-specific tables. You check to see if a person is a reviewer by joining to the Reviewers table.
You can of course add other types of people with different data to this approach. This is a good way to model things for relational bigots like me who like to avoid null-valued fields and undeclared foreign keys that reference one of several tables. MySQL jockeys would just cram it all into one big table with lots of nulls, magic numbers, and comma-separated lists in varchar fields.
If you know the requirements will not change in the future (ha!), and there is no difference in schema between users and reviewers, just use a single person_type field and keep it simple.
Never use "key ranges" for any reason. That's just hackery which will only cause a massively painful recoding effort for you or someone else in a few years. Primary keys should be without semantics and invariant.
4 Comments
CNJ
So you suggest something like renter(user_ID, user_Fname, user_Lname, user_email) and another table landlords(user_ID, landlord_ID)?
rmalayter
Yes, minus the landlord_id (it isn't needed if there is a one-to-one). Also, the answer is only yes if there is other landlord-specific data that would need to be in the landlord table(s) but not the general person tables. Lastly, I really wish folks would stop naming thier "people" tables "user"... USER, USERID, etc. are often reserved keywords or function name in most SQL dialects, so you just end up causing yourself pain. There's nothing like having to type in a bunch of extra brackets or quotes for EVERY SINGLE QUERY ever written for an application.
rmalayter
The standard super-type example in SQL books is the "customer". Customers can be people or orgainzations. So you have a single customer table with common customer fields, which gets related to orders and other customer-type data. You then have a CustomerPerson and a CustomerOrg table, which each are one-to-one with Customer and user the Customer_ID as their key. CustomerPerson and CustomerOrg contain very different schemas in most cases. A bit contrived (People and Organization would be their own tables in most real-world applications, sub-typing via a one-to-one intermediate tables).
CNJ
Understood and appreciate the tip on the User naming scheme, I haven't run into that problem in the short examples I've been working with yet.
I recommend you to create one table for users and other for usertype, with a foreign key to users, in case you later need users with both type.
Comments
Given the vagueness of your question, I would say that it depends on the kind of information that either party would be accessing/inserting. As I understand it, the structure of your database should reflect the manner in which you are organizing your data, not necessarily the relationship between the users of your database. For example, you could have a separate tabled called "User Status" with 0->Reviewer, 1->Review-ee, and then list User Status as a foreign key in your Users table as a column that indicates whether a given User is a Reviewer or a Reviewee, thereby eliminating the need for separate User tables.
Comments
You could have a column 'type' with values "reviewer" and "reviewee"
Comments
That depends rather largely on what data you're keeping about each of these user types. If very similar, a single table could be okay, but you'd want a differentiating field (column) rather than assigning a range of IDs.
If you need different information about reviewers and reviewees, and it otherwise makes sense to keep that information together, then you'd do well to separate them into at least two tables.
If the attributes describing these two different users is different, then you may want to separate them. If they share same attributes like firstName, lastName etc, then you can keep them in the same table.
Comments
One time, here on SO someone said, "ALWAYS put like things in the same table."
This is true because that SO'ers definition of 'like' is so ambiguous that he can always create a definition that's true.
So said, you should do the same here. The fact that Reviewers and Reviewee are both 'people' is irrelevant. The real question is can anyone ever be both? If that's not true then you'll have no problem and actually should use separate tables.
Think of it this way.
Let's say that's it's true reviewers can never be reviewees. Somewhere you'll have a table with both Reviewer_ID and Reviewee_ID. You'll build a FK back to those two tables. Those Constraints GUARANTEE that you'll have one reviewer and one reviewee... you'll never have 2 of one.
But if they can 'cross dress' so to speak, and you build two tables, you'll either have to put the same person in both tables with different id's an no way (besides adding more layers) to know they are the same person or else you'd have to recast to a Super-type/sub-type style.
Put like things in the same table. And what like really means is behaviorally. Things which can be reviewed should go into one table even if they are professors and films. How you handle their disparate attributes (either Nullable columns or Supertype/subtype) is determined by more information than you've provided).
