Database design, two types of users for website

Question

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?

Answer 1

You could have a column 'type' with values "reviewer" and "reviewee"

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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.