0

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?

users
id
name
password
type
driver_license_number
driver_years_driving
driver_rating
  1. 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?

users
id
name
password
drivers
id
user_id
license_number
years_driving
rating
Sergio Fonseca
  • 326
  • 2
  • 11
  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Jun 14 '22 at 21:05

3 Answers3

1

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.

MrUwugu
  • 56
  • 3
  • so, what would you suggest to avoid the scalability problem in the future? use a non-relational database like mongo? – Sergio Fonseca Jun 15 '22 at 16:11
  • 1
    @SergioFonseca yes, that is one good route to pursue! another would be vertically scaling by adding more hard drives, RAM, a faster CPU with better cooling, etc. you could also go down the route of manual sharding, denormalization, or creating read replicas or active-active cluster configurations. however at a certain point, a NoSQL database becomes less of a pain than these options. – MrUwugu Jun 15 '22 at 18:49
1

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.

credentials
id
username
password_hash (you are hashing your passwords, right?)
users
cred_id
... other user related info
driver
cred_id (you can get with user_id, but it's an unnecessary join)
user_id
... driver related info
Eduardo Thales
  • 401
  • 1
  • 8
  • thanks for the answer! I like your idea but it brings me a couple more questions... so should the users table have its own primary key? should the drivers tables have its own primary key? Later on i want to have a trips table, how should I relate this table with drivers and users? – Sergio Fonseca Jun 15 '22 at 16:34
  • 1
    Yes, they should. If you want a trips table, it can use the primary key of both users and drivers table. – Eduardo Thales Jun 15 '22 at 23:31
1

Consider 3 tables (plus, perhaps, some others):

  • Persons -- this contains the stuff that is common to both Drivers and Riders, such as login.
  • Riders -- bio, etc
  • Drivers

If it would make things handier for you, build two views:

  • Rider_all -- JOIN between Persons and Riders
  • Driver_all -- JOIN between Persons and Drivers
Rick James
  • 135,179
  • 13
  • 127
  • 222