Database Design – How to Model a Limited History of a Table

database-designddl

Context

I have 3 entities (user, shop and car), a car can only have one regNum, one shopId and one ownerId at a time, that's why they are embedded in the car table.

create table "user"
(
  id   bigint primary key,
  name varchar(40) not null
);

create table "shop"
(
  id   bigint primary key,
  name varchar(40) not null
);

create table "car"
(
  id      bigint primary key,
  ownerId bigint,
  regNum  varchar(8),
  shopId  bigint,
  price   numeric(10,2),
  constraint foreign key(ownerId) references "user",
  constraint foreign key(shopId) references "shop"
);

Problem

I want to keep history of the car's regNum, ownerId, shopId and eventually other future fields (but not necessarily all of them).
What's the best solution (scalability/performance/easeOfUse) ? I have found the ones below, maybe someone has faced the same problem, maybe there is another solution ?

Solution 1

I add as much history tables as I have fields to "watch".
This seems to be a normalized way to do the job but it also looks pretty complex to maintain, it's also more greedy as if I modify all fields at once (regNum, shopId and ownerId), I need to insert 3 records (one for each history and so on if I watch other fields later).

create table "carOwner"
(
  id        bigint primary key,
  carId     bigint not null,
  changedAt timestamp not null,
  ownerId   bigint,
  constraint foreign key(carId) references "car",
  constraint foreign key(ownerId) references "user"
);

create table "carShop"
(
  id        bigint primary key,
  carId     bigint not null,
  changedAt timestamp not null,
  shopId    bigint,
  constraint foreign key(carId) references "car",
  constraint foreign key(shopId) references "shop"
);

create table "carRegNum"
(
  id        bigint primary key,
  carId     bigint not null,
  changedAt timestamp not null,
  regNum    varchar(8),
  constraint foreign key(carId) references "car"
);

Solution 2

I keep history in a single table, which is a light snapshot of the car table at a given time. It seems easier to maintain but it's not precise as I cannot directly see what changed if I don't have previous record.

create table "carHistory"
(
  id        bigint primary key,
  carId     bigint not null,
  changedAt timestamp not null,
  ownerId   bigint,
  regNum    varchar(8),
  shopId    bigint,
  constraint foreign key(carId) references "car",
  constraint foreign key(shopId) references "shop",
  constraint foreign key(ownerId) references "user"
);

Best Answer

Not sure I understand your initial design:

create table car
( id      bigint primary key
, ownerId bigint
, regNum  varchar(8)
, shopId  bigint
, price   numeric(10,2)
,    constraint foreign key(ownerId) references user (id)
,    constraint foreign key(shopId) references shop (id)
);

If for example, a car_owner is not mandatory, you may consider moving it to a separate entity:

create table car
( car_id bigint primary key -- vin is a standard identifier for a car
, -- other mandatory car attributes
);

create table car_owner
( car_id bigint not null 
      references car (car_id)
, owner_id bigint not null
      references owner (owner_id) -- user?
,   primary key (car_id)
);

etc for other non-mandatory attributes

The history for the owner of a car:

create table car_owner_history
( car_id bigint not null 
, owner_id bigint not null
, begin_time timestamp not null
, end_time timestamp
,     primary key (car_id, begin_time)  
);

car_owner_history can be maintained via triggers. When a car is assigned it's first owner

insert into car_owner (car_id, owner_id)
values (c_1, o_1);

This results in the following change in history 

insert into car_owner_history (car_id, owner_id, begin_time)
values (c_1, o_1, t_0);

When car changes owner from o_1 to o_2

update car_owner set owner_id = o_2
where car_id = c_1 and owner_id = o_1;

The following change is made in history:

update car_owner_history
    set end_time = t_1
where car_id = c_1 
  and owner_id = o_1
  and end_time is null;

insert into car_owner_history (car_id, owner_id, begin_time)
values (c_1, o_2, t_1);

When a car no longer has an owner end_time is updated to reflect that.

Who owned car c1 at time t_x?

select owner_id 
from car_owner_history
where car_id = c_1
  and t_x between begin_time and coalesce(end_time, t_x)

What owners have car c_1 had?

select owner_id 
from car_owner_history
where car_id = c_1
order by begin_time;
Related Question