Multiple Foreign keys in One Table reference a Primary Key in another Table?

[BenzZz]

I was wondering if the following in terms of tables and keys is possible:

Login(username, date_time_login)

Customer(username, surname, ...)

Driver(username, surname, ...)

Admin(username, surname, ...)

Booking(b_no, cust_id, driver_id, ...)

Primary keys are underlined and foreign keys are in italics.

I want the Customer, Driver and Admin username primary key fields to refernce the Login username primary key field. Also, i want the Booking cust_id to reference the Customer username primary key field and Booking driver_id to reference the Driver username primary key field. Is this possible?

Or is there a better way to organise the tables in terms of logins and bookings to avoid complications?

Thanks

[smantscheff]

What makes a customer a customer or a driver a driver if not the fact that the customer books a driver. So what is the use of the customer and driver tables? They are just users and should be stored in a common table, sharing a lot of attributes - the all have names, addresses etc. The admin is just another user with some special rights - there should be a flag in the user table denoting if a user is an admin.
Otherwise you will get redundant data as soon as an admin decides to patronize the establishment as a customer or to get a 2nd job as a driver.

[BenzZz]

Okay, but if i do that is it okay to reference both the cust_id and driver_id foreign keys in the booking table to the same primary key (e.g. member_id or username)in a members table?

[smantscheff]

Yes, that is no problem, in one table you can have several different foreign keys pointing to the same foreign table.

[BenzZz]

Ah right, thanks. I'm not sure whether to merge customers, drivers and admin yet as they do have some attributes that do make them unique and there is an assumption that a customer or admin will not be a driver and vice versa. I guess in that case their 'role' would be updated or a new record created.

[smantscheff]

In my opinion you have to merge all members with shared attributes in one table. Otherwise you might get member Charles, 101 High Street, in the Customers as well as in the Admin table. This would contradict the paradigm of non-redundant data storage. If you still would like to separate other attributes in separate tables, those should contain foreign keys pointing to the member table.

[BenzZz]

If i create one table for all site members (customers, drivers and admin) as for instance a customer would not have a job_start_date unlike the driver and admin, could i create another table memberJob that also has the same primary key as member and references the member table but has more specialised attributes?

[smantscheff]

Looks more clean to me. That way you avoid doubling of attributes and keep their semantic limitations.

[BenzZz]

Okay, think i heard it could be done that way, table-splitting or something.

Thanks for all of the help :) much appreciated.