mercredi 15 juin 2016

Nullable FK vs two tables

I have a requirement to store all scan (barcode/fingerprint etc) events to a table, and if it's a scan that is linked to a person who is in the database, link the scan to that person. So, I need to store invalid scans as well.

I have a few options.

A UserScan table, with the details about the scan, and a nullable UserID foreign key to the User table, which is only populated when we have a valid scan.

or

A UserScan table with all the details about the scan, and a NOT NULL FK to the User Table, and only write valid scans to this table, plus a 'failed scan' table, with details about the scan only.

Maybe there's other options?

This table will be rather large, and a lot of queries will be using this table to work out who is in what location at certain times, for example. Will the nullable UserID be a less optimal option? Or is that recommended?

I was thinking of maybe a 'Scan Event' table, which has all the scans, and IF the scan is successful, a UserScanEvent table, with the Scan Event ID and the User ID. But that looks like a many-to-many link table.

I'm looking for the best design for efficiency. Which is the best way to go, and maybe there's a better idea?

Aucun commentaire:

Enregistrer un commentaire