sql - Nullable FK vs two tables -


i have requirement store scan (barcode/fingerprint etc) events table, , if it's scan linked person in database, link scan person. so, need store invalid scans well.

i have few options.

a userscan table, details scan, , nullable userid foreign key user table, populated when have valid scan.

or

a userscan table details scan, , not null fk user table, , write valid scans table, plus 'failed scan' table, details scan only.

maybe there's other options?

this table rather large, , lot of queries using table work out in location @ times, example. nullable userid less optimal option? or recommended?

i thinking of maybe 'scan event' table, has scans, , if scan successful, userscanevent table, scan event id , user id. looks many-to-many link table.

i'm looking best design efficiency. best way go, , maybe there's better idea?

option 1:
think select efficiency point of view, option 1 might give best result, using filtered index on userid column in userscan table.

option 2:
second option worst option since means have 2 identical table structures in database, , that's bad design.

option 3:
on academic level, option 3 way go. nullable columns frowned upon in relational model. prevent userscanevent table creating many many relationship between scans , persons, add unique constraint on scanid column. way, each scan in table can associated single person.

to sum up:
having said that, select efficiency tricky thing predict. hunch tells me option 1, while might not best fit relational model, enable faster select executes option 3, jorge campos wrote in comment, merely opinion, it's not can sure.


Comments

Popular posts from this blog

sequelize.js - Sequelize group by with association includes id -

android - Robolectric "INTERNET permission is required" -

java - Android raising EPERM (Operation not permitted) when attempting to send UDP packet after network connection -