Design of MySQL DB to avoid having a table with mutually exclusive fields

Question

I'm creating a new DB and I have this problem: I have two type of users that can place orders: registered users (that is, they have a login) and guest users (that is, no login). The data for registered users and guest users are different and that's why I'm thinking of using two different tables, but the orders (that share the same workflow) are all the same, so I'm thinking about using only one table.

I've read here and here (even if I don't understand fully this example) that I can enforce a MySQL rule to have mutually exclusive columns in a table (in my case they'd be "idGuest" and "idUser") but I don't like that approach.

Is there a better way to do it?

Answer 1

There are several approaches, which depends on the number of records and number of unique fields. For example, if you would say they differ in only two fields, I would have suggested that you just put everything in the same table.
My approach, assuming they differ a lot, would be to think "objects":
You have a main user table, and for each user type you have another table that "elaborates" that user info.

Users
-----

id,email,phone,user_type(guest or registered)

reg_users
---------

users_id, username,password etc.....

unreg_users
-----------

user_id,last_known_address, favorite_color....etc

Where user_id is foreign key to users table

Answer 2

I think I would have three tables :

• A user table, that would contain :
  • One row for each user, no matter what type of user
  • The data that's present for both guests and registered
  • A field that indicates if a row corresponds to a registered or a guest
• A guest table, that would contain :
  • One row per guest user,
  • The data that's specific to guests
• And a registered table, that would contain :
  • One row per registered user,
  • The data that's specific to registered users

Then, when referencing a user (in your orders table, for example), you'd always use the id of the user table.

Answer 3

Sounds like mostly a relational supertype/subtype issue. I've answered a similar question and included sample code that you should be able to adapt without much trouble. (Make sure you read the comments.)

The mildly complicating factor for you is that one subtype (guest users) could someday become a different subtype (registered users). How you'd handle that would be application-dependent. (Meaning you'd know, but probably nobody else would.)

Answer 4

The foreign key in the Orders table pointing back to the Customer entity that placed the order is typically a non-nullable column. If you have two different Customer tables (RegisteredCustomer and GuestCustomer) then you would requiree two separate nullable columns in the Orders table pointing back to the separate customer tables. What I would suggest is to have only one Customers table, containing only those rows (EDIT: sorry, meant to write only those COLUMNS) that are common to registered users and guest users, and then a RegisteredUsers table which has a foreign-key relationship with the Customers table.

Answer 5

What you are describing is a polymorphic table. It sounds scary, but it really isn't so bad.

You can keep your separate User and Guest tables. For your Orders table, you have two columns: foreign_id and foreign_type (you can name them anything). The foreign_id is the id of the User or Guest in your case, and the content of the foreign_type is going to be either user or guest:

id  |  foreign_id  |  foreign_type  |  other_data
-------------------------------------------------
1   |  1           |  user          |  ...
2   |  1           |  guest         |  ...

To select rows for a particular user or guest, just specify the foreign_type along with the ID:

SELECT * FROM orders WHERE foreign_id = 1 AND foreign_type = 'guest';