Relational database design multiple user types

by

Your case looks like an instance of class/subclass.

There are two classic ways to design SQL tables to deal with subclasses. Each has advantages and disadvantages.

One way is called “Single Table Inheritance”. In this design there is just one table for all types of users. If a given column doesn’t pertain to a given row, the intersection is left NULL. A column can be added to indicate the user type.

Another way is called “Class Table Inheritance”. This is much like the answer Nanego gave, with a few minor changes. There is one table for users, with all the common data, and a id field. There is one table for each subclass, with data that pertains to that subclass. The id field is often set up as a copy of the id field in the matching row back in the users table. This way the subclass key can do double duty, acting as both a primary key and as a foreign key referencing the user table. This last technique is called “Shared Primary Key”. It requires a little programming at insert time, but it’s well worth it. It enforces the one-to one nature of the relationship, and it speeds up the necessary joins.

You can look up all three of these designs as tags in SO or as articles out on the web.

single-table-inheritance
class-table-inheritance
shared-primary-key

More Related Contents:

  1. Modeling Product Variants
  2. Proper database model for a user feedback system (an interesting case)
  3. Inventory management with stock options
  4. Cakephp Career Database Design
  5. How to store multiple options in a single table?
  6. What is the ideal data type to use when storing latitude / longitude in a MySQL database?
  7. What does character set and collation mean exactly?
  8. How to pivot a MySQL entity-attribute-value schema
  9. Implementing Comments and Likes in database
  10. MySQL: multiple tables or one table with many columns? [closed]
  11. Is it possible to query a tree structure table in MySQL in a single query, to any depth?
  12. MySQL – Conditional Foreign Key Constraints
  13. Auto Generate Database Diagram MySQL [closed]
  14. Unique constraint that allows empty values in MySQL
  15. Entity-Attribute-Value Table Design
  16. In SQL, is it OK for two tables to refer to each other?
  17. Storing SHA1 hash values in MySQL
  18. How to create relationships in MySQL
  19. Remove Primary Key in MySQL
  20. Facebook like notifications tracking (DB Design)
  21. How to design a multi tenant mysql database
  22. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  23. Difference between two table structure
  24. MySQL query, MAX() + GROUP BY
  25. When to use a left outer join?
  26. MySQL: how to do row-level security (like Oracle’s Virtual Private Database)?
  27. List of Constraints from MySQL Database
  28. Should I always prefer MySQL InnoDB over MyISAM?
  29. How to store a one to many relation in MySQL database?
  30. MySQL Relationships

Leave a Comment