MySQL 2 foreign keys in 1tbl referencing 1 primary key

I have 2 tables in my DB, and I can't quite define the relations correctly:

1. tbl_users holds some info about system users AND admins (I "packed" 2 different tables - tbl_users and tbl_admins - together, be开发者_Go百科cause there was about 90% overlapping of attributes. Main difference between attributes is sorted by introducing an attribute with boolean data type)*

2. tbl_orders *holds some info about orders that users have created, and reference to tbl_users, to see which user created which order. Also, this table should hold the reference to admin who did the order processing. Info on admin is stored in tbl_users.*

table description:

**tbl_users**
id int pk
name varchar
address varchar

**tbl_admins**
id int pk
name varchar
address varchar

these two packed together look like this:

**tbl_users**
id int pk
name varchar
address varchar
user boolean
admin boolean

problems start here:

**tbl_orders**
id int pk 
amount int
processed boolean
user_id - references tbl_users, but only the user that is user boolean=1
admin_id - references tbl_users, but only the user that is admin boolean=1

Another possibility:

Name that "combined users" table to "persons", holding the info about any user/admin/whatever-other-usertype has.

**tbl_persons**
id int pk
name varchar
address varchar

Then, have another table that stores only the info that a person is actually a user (and another table for admins):

**tbl_users**
id int pk (NOT auto_incremented), fk references tbl_persons(id)

**tbl_admins**
id int pk (NOT auto_incremented), fk references tbl_persons(id)

Then, it's easy to define the foreign key constraints and enforce the integrity you describe:

**tbl_orders**
id int pk 
amount int
processed boolean
user_id fk references tbl_users(id)
admin_id fk references tbl_admins(id)

The second foreign key can be easly enforced on the database by referencing the tbl_admins. The foreign key to only users will have to be enforced on application code.

This can be solved by creating two more table:

***tbl_user_order *** 
user_id --> foreign key: tbl_users.id 
order_id --> foreign key: tbl_orders.id


*** tbl_admin_order ***
user_id --> foreign key: tbl_users.id 
order_id --> foreign key: tbl_orders.id

Thay might look the same, but they aren't. The first keeps data on which user created which order, and the second on which admin passed the order.

Or, you can aggregate the two tables above into one table, saying like this:

***tbl_user_order *** 
user_id --> foreign key: tbl_users.id 
order_id --> foreign key: tbl_orders.id
admin boolean

In the table above, the admin boolean says that one particular user from tbl_user is in fact the admin which processed the order (for admin = 1). For admin = 0, the user id indicates the user creating the order.

Hope this helps.