-1

Precursors: I have read Foreign key constraints: When to use ON UPDATE and ON DELETE & the database I'm working with is mySQL.

I have a project where I have to create a database very similar to tracking the usage of a shared car (think Turo or Zipcar, any generic car sharing service). Cool, I have done that, and it's working. I have a database, I have a table of users, a table of cars, and a table of locations. Each entry in the location table has a column for the carID, the location, and the userID of who was using the car at that GPS location (1m between each location update, but we're going to reduce it to 1s eventually). This makes it easy to plot each user's route because they can search the locations table for all the locations where their userID shows up.

The next part is where I need some advice. Before a user can drive a car, the database updates the cars table with the user's ID. That car is considered checked out when the userID is not NULL. When the user starts driving the location data starts coming in. On every location update I have a 'before insert' trigger that makes another query searching the cars table for the current usedID, and inserts the ( carID | timestamp | GPS location | current userID ) into the locations table. I'm not currently using foreign keys, but I'm trying to see if this would be a case where they could help me. I want to reduce the overhead of performing an additional query every time there's a GPS location update. I'm afraid of any weirdness where updating the userID in the cars table will cascade into the locations table though. Because I really don't want all the previous GPS locations being updated to belong to the new userID.

Assuming I use a foreign key for userID in the locations table that references the cars table userID...

  • I can't use ON UPDATE RESTRICT, because every user change would fail because each usedID will have at least a few locations attached to it.
  • I can't use ON UPDATE NO ACTION, as it's the same as above.
  • I can't use ON UPDATE CASCADE, because the new user will be assigned all the previous user's locations.
  • I can't use ON UPDATE SET NULL, because then it will just clear the userIDs from the locations table.

I don't know what a better solution would look like. I'm thinking I can't use foreign keys, but I do need to associate a user with a specific location entry. So what might be the best way of going about that? I'm completely open to new table organizations, or new schemas.

user554242
  • 5
  • 3
  • 2
    I don't think you understand what `ON UPDATE` is about. It's only triggered if you change the ID of a record in the master table, not in the referencing table. This should never happen. – Barmar Jan 12 '21 at 06:51
  • FKs provide an INDEX (which could be added anyway); this provide performance in JOINs. FKs also provide a constraint to check integrity. FKs optionally provide CASCADEing operations, which you could do independently. When FKs don't work for you, abandon them. – Rick James Jan 12 '21 at 18:08

1 Answers1

0

Your current data model is not optimal. To avoid the overhead of before insert trigger (and the additional query) you should introduce a new table, called reservations. The userID column does not belong to the cars table - to indicate who is currently using the car you will use the new reservations table which should have the following columns:

  1. car_id - integer, FK into cars table
  2. user_id - integer, FK into users table
  3. usage_period - tstzrange

There should be covered by an exclusion constraint

EXCLUDE USING GIST (car_id WITH =, user_id WITH =, usage_period WITH &&)

to prevent using the same car by multiple users at the same time or the same user driving different cars at the same time.

When you need to know who was driving the car at a given location (or a list of location points) - you will simply join with the reservations table.

IVO GELOV
  • 13,496
  • 1
  • 17
  • 26