Best practice for database where the values can't change

Question

Suppose I have a table Inspection with a field for Place (the location where the Inspection occurred). The field is a foreign key to a Place table, where each place has a Name, among other things. The Place names can change over time (for instance the Place could be a restaurant where the name changes when its ownership changes), but we would like the Inspection to continue to hold the original value it was given when it was created.

Is there a best practice for something like this? One thought is to have two fields, one to store the foreign key, regardless of whether it's been updated, and another to hold a string value for the original Place name. Another thought is to simply make Places immutable. You would have to create a new Place every time the name changed, rather than updating existing Places. Perhaps you could even related one or more Places somehow so that you know that multiple rows in the table actually relate to the same physical location.

score 1 · Answer 1 · answered Mar 11 '13 at 11:55

I suggest you to create a new table, InspectionHistory or something like this, with the following structure:

InspectionID|PlaceID|DateStart|DateEnd|Status

Here you would have foreign keys with your Inspection table and Place table and the information, you want to store. With such structure you can store several record about your Inspection places.

score 1 · Accepted Answer · answered Mar 11 '13 at 14:21

There's a logical difference between a business and its current location.

When you're talking about inspections, it usually makes sense to store both. The inspection history of an address is meaningful, regardless of who occupies that address. And the inspection history of a business is meaningful, too, regardless of the location that business operates in.

Referenced tables not shown, but should be obvious.

create table inspections (
  address_id integer not null references addresses (address_id),
  inspection_date date not null default current_date,
  -- Instead of business name, you could (should?) store the licensed business
  -- identifier, if the local government issues one.
  business_name varchar(35) not null default 'Unoccupied',
  inspector_id integer not null references inspectors (inspector_id),
  primary key (address_id, inspection_date)
);

I'd probably want to revoke permissions on this table, and allow inserts only through a stored procedure. The goal would be to make sure that application code didn't accidentally omit the business name (or the licensed business identifier, whichever).

You probably can't have a foreign key based on business name; business names aren't guaranteed unique over time, and foreign keys have to reference columns that have a unique constraint. (There are ways to work around that--adding date columns and additional, overlapping unique constraints, but that might be more trouble than it's worth.) In any case, you can't cascade updates. As a historical record, the data in this table has to express the facts at the time of the inspection.

Best practice for database where the values can't change

2 Answers2