Geek asks and answers

Designing a MySQL database to avoid a table with mutually exclusive fields

I am creating a new database, and I have this problem: I have two types of users who can place orders: registered users (that is, they have a login) and guest users (that is, without a login). The data for registered users and guest users is different, and therefore I think about using two different tables, but the orders (which use the same workflow) are the same, so I think about using only one table.

I read here and here (even if I do not fully understand this example) that I can force the MySQL rule to have mutually exclusive columns in the table (in my case it would be "idGuest" and "idUser") but I do not like this approach.

Is there a better way to do this?

+3
source share
5 answers

Sounds like a relational supertype / subtype problem. I answered a similar question and included a sample code that you can adapt without any problems. (Be sure to read the comments.)

, ( ) - ( ). , . (, , , , .)

+1

, . , , , , .
, , , "":
, , "" .

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

user_id users

+2

, :

  • A user , :
    • , ,
    • ,
    • , ,
  • A guest, :
    • ,
    • ,
  • registered, :
    • ,
    • ,

, (, orders), user.

+1

"", ​​ "", , . Customer (RegisteredCustomer GuestCustomer), Orders, . , , , Customers, (EDIT: , COLUMNS), , RegisteredUsers, Customers.

0

What you are describing is a polymorphic table. That sounds scary, but it's really not that bad.

You can store separate user and guest tables. For the Orders table, you have two columns: foreign_idand foreign_type(you can name them anything). foreign_idis the user or guest identifier in your case, and the content foreign_typewill be either user, or guest:

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

To select rows for a specific user or guest, simply specify foreign_typealong with the identifier:

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

More articles:


All Articles