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