0

I have an application that deals with businesses and their physical locations. So most tables have a location_id field that logically partitions the data.

One table deals with marketing referrals and each location has a set of sources that they can define in the application. However, ALL locations have an "Unknown" source that is basically a catch-all when the source isn't known. The marketing referral id is the primary auto-incrementing key on the table, and the location_id is a foreign key to the locations table.

As its set up right now, there is an "Unknown" record for each location in the table, each with its corresponding location_id. This means there are a bunch of duplicative records (aside from the location_id). Is this the best possible design for this scenario? I don't really want to remove the foreign key constraint, but in a perfect world I'd see there being a record with an ID of 0 that would serve as the "unknown" record for all the various locations.

Am I overthinking it?

Improve this question

1 Answer 1

Reset to default
1

Having a "dummy" location with id=0 to represent the unknown location is possible.

My personal solution would however to just use NULL for that purpose, i.e. set the location_id to NULL to reflect unknown locations. That will be simpler since you will not need the dummy location "0". Also it should be more efficient, as the foreign key constraint will not be checked when you insert or update a row with an unknown location. And if you have an index on location_id, then that index will not contain any key for the rows with NULL location ids. This is more efficient than having a large set of duplicates for location id "0".

After all, that is what NULLs are for: to represent the absence of information (whether that information is unknown or just does not apply).

The flip side is that your application must be ready to handle nulls. Looking up all records for a given location id remains the same. Looking up records with an unknown location will need changing: from WHERE LOCATION_ID=0 to WHERE LOCATION_ID IS NULL.

Improve this answer
2
  • I’ll need to look into that....I thought a foreign key constraint wouldn’t allow nulls.
    – hyphen
    Commented Feb 12, 2019 at 11:10
  • Nulls are perfectly acceptable in foreign keys. They are not allowed in primary keys.
    – Albert Godfrind
    Commented Feb 12, 2019 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.