I'm developing an Uber like app using Laravel, as you may know it has different user types, there can be drivers and regular users. i'm not sure how to structure the database since drivers can have other fields and relations that regular users do not but i need both types to be able to login. Also users can take a drive and rate the driver and only drivers can have their bio, license number, years driving, rating and just them can have relations like to the car the are driving and so on...

I want to know your thoughts about what is the best approach to handle this type of situation?

1. Keep drivers and users in the same users table with the drivers fields nullable and a type field to know if it is a driver or a regular user?

Q: If I go with this option how can I guarantee the driver of a ride is effectively a driver and not a simple user?

2. Keep credentials of both drivers and regular users in the users table and store drivers specific info in another?

Q: If I go with this option should drivers have their own primary key or use the user's primary key? which table should keep the 1:1 relationship? the users table? the drivers table?

CodePudding user response：

I believe you should use two separate tables. This would avoid having lots of nullable fields that are not shared between riders and drivers. Furthermore, if these entities are frequently changing, ALTER TABLE will be a bit of a pain at scale.

Joins of course are a little bit more expensive across the two tables, but the query is more natural to write because of our normalization choice.

As a side note, this application will eventually have trouble scaling no matter which way you choose to write the tables, because MySQL cannot easily be horizontally scaled.

But, if you want easy querying and avoidance of nullable fields, two separate tables sounds like the right choice to me.

CodePudding user response：

You are tying two different things together under the term user: user as in “someone who registered in my application” and user as in “someone who’s using my application to get rides”. Both drivers and non-drivers are users in the first definition, but not in the second.

What's confusing is that the Driver entity is just a User entity with more fields, so it's possible to not represent the entity at all, just add more columns to the User entity, and, responding to your first question, add a is_driver column to tell which entity is which.

By doing this, you are crippling your database capabilities to guarantee your data is valid. You now can have a Driver row without a driver_license_number, because your database doesn't know what a Driver is, oops.

There's a lot of benefits by being explicit in your database schema. Part of the database work is to guarantee data consistency, help your database help you.

My suggestion is to go a step further. Credentials are one thing, they get their table. Users are another, they get their table (in your example, users seems to have no data at all, but they will probably have more things than just their name). Drivers are yet another, they get their table too.