Boolean being true if record is referenced in another table

SandrinoM

If I have, let's say, table 1 and table 2. If a record with the ID of 3 in table 1 is referenced in table 2, then a boolean would be set to true if it's referenced in that specific table. Is this possible? (Or does that make sense cuz that's kinda hard for me to understand and I'm writing it)

arturosanz

I'm not sure if I understood well what you said, but it seems to me that you just need to know whether a record in the 1st table is referenced in the 2nd table or not. To do so, you can use the "Has Record" function which will return true if there is a reference in the 2nd table to the record in the 1st table or false if not.

Then, if you want to store the result in a boolean field in the 1st table you can do it, but be careful when you do so, because you will have to maintain the integrity of the data. This means that any change in table 2 which could affect to the boolean field in table 1 should also update it accordingly. Maybe it's better to not use the boolean field but call the "Has Record" function any time you need to check whether a reference exists or not. If the table 2 has many records maybe you should create an index by that field to make faster queries.

arturosanz

This is the "Has Record" function:

I'll give you an example. Let's say we have a table for drivers (table 1) and cars (table 2). Both are related because the cars table has a driver_id field which references the id field in the drivers table. Now we want to make an endpoint to add a new record in the cars table, and the input must provide some data about the car: make, model, driver_id, and mileage. However, before creating the record we want to make sure that the driver_id supplied in the input really exists in the drivers table. We first check if the driver_id exists with the Has Record function, then we use the Precondition function to throw an error if the Has Record function doesn't return true. Returning true means that the driver_id exists, so it makes sense to create de car in the car table because it will be correctly associated to an existing driver.

Because we have the driver_id saved in the car record, it's easy to check at any time whether the driver_id exists or not in the drivers table by calling the Has Record function. We could also have a boolean field checked in the car record to know that there is a reference to the drivers table, but we don't need it, because we already have the driver_id field too, which is the proof of the connection between the two tables. We can never be sure if the referenced driver record will be deleted eventually, but we can check whether it still exists or not with the Has Record function any time we need to, because we have the driver_id saved in the car record.

SandrinoM

As much as I would love to understand that, I have no idea what any of that means 😭

SandrinoM

Thank you so much!