1

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"
);
  • if I don't have previous record: You have the previous record so it's not an issue. And if you don't, it means it's the 1st history row, so it's the data when creating the car (so "what have changed" has no real meaning there) – Xenos Jun 28 '18 at 11:10
  • 1
    The "right" answer depends heavily on why you need this history and how you intend to use it (what kind of queries). – RBarryYoung Jun 28 '18 at 14:37
  • @Xenos, I mean that all historized fields are in the same table so if only one field changed, how do I know that the others have changed or not, if you only take one row without looking others, you cannot tell which field has changed, in the first solution, a row is a change so you are sure that only this field changed. – Karl.S Jun 28 '18 at 17:28
  • @RBarryYoung, nothing tricky, just for keeping a track of previous valuable information. – Karl.S Jun 28 '18 at 17:30
  • That doesn't really tell us anything. – RBarryYoung Jun 28 '18 at 20:51
3
+50

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;
  • That looks like the solution 1, but why are you using both the car_owner and car_owner history since they duplicate car/owner relation ? the car_owner_history is enough to have the car/owner relation plus the relations history. – Karl.S Jun 24 '18 at 17:30
  • It's just a matter of convenience for whom that maintain car_owner. You can, of course, maintain the history relation directly. Note that I have an interval(begin_time, end_time) which makes it easier to answer questions like: who owned most cars at a certain point in time – Lennart Jun 24 '18 at 17:39
  • 1
    Another difference between my suggestion and your solution 1 is that when a car_owner is removed in solution 1, it's history vanishes – Lennart Jun 24 '18 at 17:43
  • Nope, if a car owner is removed, then you can set ownerId to NULL since this field is nullable (example: insert into carOwner (carId, ownerId, changedAt) values (1337,NULL, now()). – Karl.S Jun 25 '18 at 17:09
  • 2
    You might end up with inconsistent data since you've put a PRIMARY key on var_id, owner_id: a car might appear twice there. PRIMARY key must be the car_id and you might add an extra non-unique KEY for car_id, owner_id (but it's useless since your REFERENCE on owner_id will auto create an index on that column). Same goes for car_owner_history: you can end up with two equals car_id, begin_id and your history is broken – Xenos Jun 29 '18 at 8:05
2

I have used option 2 for many projects that require historically tracking of data changes. The way I have captured the data is on the insert/update of the current state table I’ll take the current values for the columns I’m tracking and insert a new record in the journaling table. This allows me to load the data from just one table and let the application layer highlight the changes from one record to another.

  • But is it better than the solution 1 in terms of performance, flexibility, ease of use... I mean I don't want to be blocked later with something I've missed or didn't know that would happen. – Karl.S Jun 24 '18 at 17:32
  • If all you are interested in is the changes about the car’s attributes then this will cover that. You can add more attributes that you want to track at any point they will just need to be nullable as you won’t have the historical data to fill it in for existing records. For performance you only need to have two indexes, one clustered on the primary key and one on carId to retrieve the history. This also helps in performance in my opinion as you have one table with no joins vs three tables with two joins. If you can define how you will create the history report that might make a difference. – Aaron Jun 24 '18 at 19:31
  • 2
    Additionally, while you can create reports from sql statements I always find much more flexibility in just pulling the data and creating the report at the application layer. You can then get the additional benefit of being able to cache the results in an in-memory solution that will help reduce load on your database. – Aaron Jun 24 '18 at 19:37
  • 1
    @KarlStein You'll put TRIGGER to make the history: the less queries TRIGGER will do, the better. TRIGGER got executed for each row one at a time, which is utterly slow with batch INSERT/UPDATE so having only one history table to insert into will certainly be fastest. – Xenos Jun 29 '18 at 8:07
0

I think generally a car's usable life is probably 10 to 20 years, so it means a car may only have very limited # of owners.

If this assumption is correct, I would design the [car] table by adding two more columns (and one optional column) as follows

create table "car"
(
  id      bigint primary key,
  ownerId bigint,
  regNum  varchar(8),
  shopId  bigint,
  price   numeric(10,2),
  OwnerStartDate datetime default getdate(),
  OwnerEndDate datetime,
  PrevCarID bigint, -- this is optional if you want to track this car 
  constraint foreign key(ownerId) references "user",
  constraint foreign key(shopId) references "shop"
);

If a car changes ownership, you just need to update the OwnerEndDate, and insert a new record to car table, with OwnerEndDate being null, meaning this car is not "history" yet, and for this new car record, you can update PrevCarID column to the ID column of the record which you just update its OwnerEndDate column.

This way, the table [car] can be both a history table and a live OLTP table. As mentioned in the assumption, for a same car, it should not generate too many records.

The advantage of this design is its simplicity.

  • Sorry but this only keeps the last entry of the history and I need the full history, don't focus on car/owner context it is just for example, what I need is a way to keep full history of selective changes of a table. – Karl.S Jun 24 '18 at 17:23
  • I do not understand why you say it only keeps the last entry of the history. It actually, in theory, keeps the whole history of a car's property. For example, if a car's property changes, you update the enddate and start a new entry. Of course, if your intention is not about car/owner, we may need to reconsider the solution and see whether it will cause too much entries. – jyao Jun 25 '18 at 4:27
  • Since a car is identified by its ID (bigint primary key), your solution consist of creating a new record of the same car with a different ID which is a bad pratice in this case because it breaks the consistency of the relations, plus to find all owners of a car you need to "loop select" on each records to get the PreviousCarID, because the field regNum should not have duplicate in this table. – Karl.S Jun 25 '18 at 17:03
  • Ok, I got your point. I did not know ID (the PK) is for car identification, I thought it would be VIN that is for car identification. I actually implemented a very similar data model a few years ago. In that table (for car lease business application), I was using the model I mentioned in my answer. – jyao Jun 25 '18 at 22:03
  • So in your case it was a good solution, but I repeat I gave the "car/owner/shop" context as an example, actually the focus of this issue is really on the history aspect, Thank you for you help though. – Karl.S Jun 26 '18 at 1:33
0
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,
ModelNumber,
Color
  RegNum  varchar(8),
  price   numeric(10,2),
  );

Car table contain car specific info. This way it can cover any Biz requirement. Like Maintainance,repair,servicing etc.

This way your fist statement is wrong.

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.

Agree that "a car can only have one regNum, one shopId and one ownerId at a time" but they cannot be embed in car table for this reason alone.

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

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

I am not sure if regNum should be in Current_car_Status or Car_Status_History

When volume of data will be in millions and there is very frequent query in Car Status table then you should create History table, else you should keep one table.

CARID,shopid,ownerid,regnum can be composite clustered index.But not necessary that you create them since each are reference as foreign key,index will be utilize if join them with their respective table.

Car table can have indexes on other attributes depending on the requirement.

Application layer/Report are correct medium to show owner change history in user friendly manner.

Car table should no contain shopid or Ownerid.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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