CODEWITHSUNDEEP
databasedatabase-designentity-relationship
Mark
Mark

Reputation: 1316

Is this a valid use of NULL foreign keys?

Consider this very small contrived subset of my schema:

SensorType1
ID : PK

SensorType2
ID : PK

Reading
Timestamp
Value
SensorType1_ID FK -> SensorType1
SensorType2_ID FK -> SensorType2

Some readings are for SensorType1, some are for SensorType2. I would probably add a constraint to ensure exclusively one of those FK's is always pointing somewhere.

I've read a lot in the past about NULL FK's being very bad design, but I've been wrestling with my schema for days (see previous posts) and no matter which way I twist and turn it, I either end up with a NULL-able FK somewhere, or I have to duplicate my reading table (and it's dependants) for every sensor type I have (3).

The above just seems to solve the problem nicely, but it leaves a not-so-nice taste in my mouth, for some reason. It is the ONE place in my entire schema where I allow NULL fields.

I thought a bit of peer review would help me accept it before I move on.

Thanks!

Upvotes: 0

Views: 131

Answers (2)

Ess Oh Hader
Ess Oh Hader

Reputation: 31

First PK's as just "ID" mean they have to change names constantly throughout the model. It makes following the RI difficult. I know some people like that. I hate it because it prevents an automated approach to finding columns.

I do things like this

SELECT * FROM ALL_TAB_COLUMNS WHERE Column_Name = :1;

If you need to "role play" have the same FK twice in a table then 

LIKE '%' || :1 should work.

But you're changing col names even when not forced to. ID becomes Location_ID and then becomes LoggingLocation_ID for no technical reason

I'm assuming this isn't a physical model. If it is, why are you vertically partitioning LiveMonitoringLocation and HandProbingLocation? Is it just to avoid a nullable column? If so you're utility function is all messed up. Nullable columns are fine... adding a new table to avoid a nullable column is like driving from NYC to Cleveland to Boston in order to avoid any red lights.

Upvotes: 0

Mike DeSimone
Mike DeSimone

Reputation: 42805

What is wrong with doing it like:

Sensor
  ID: PK
  ... common sensor fields ...

SensorType1
  ID: FK(Sensor)
  ... specifics ...

SensorType2
  ID: FK(Sensor)
  ... specifics ...

Reading
  ID: PK
  Sensor: FK(Sensor)
  Timestamp: DateTime
  Value: whatever

Upvotes: 2

Related Questions