主页 / dba / 问题 / 208308
Accepted
Karl.S
Asked: 2018-05-31 16:53:49 +0800 CST

如何对表的有限历史建模

  • 772

语境

我有 3 个实体（用户、商店和汽车），一辆汽车一次只能有一个regNum、一个shopId和一个ownerId，这就是它们嵌入汽车表的原因。

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"
);

问题

我想保留汽车的历史regNum，ownerId以及shopId最终其他未来领域（但不一定是所有领域）。什么是最好的解决方案（可扩展性/性能/易用性）？我找到了下面的那些，也许有人遇到过同样的问题，也许还有另一种解决方案？

解决方案 1

我添加了与要“观看”的字段一样多的历史表。这似乎是一种标准化的工作方式，但它看起来维护起来也很复杂，它也更贪婪，好像我一次修改所有字段（regNum、shopId 和 ownerId），我需要插入 3 条记录（每个记录一个历史等等，如果我稍后看其他领域）。

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"
);

方案二

我将历史记录保存在一个表中，它是car表在给定时间的简单快照。它似乎更容易维护，但它并不精确，因为如果我没有以前的记录，我无法直接看到发生了什么变化。

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"
);
database-design ddl

4 个回答

  1. Best Answer

    不确定我是否理解您的初始设计：

    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)
);

    例如，如果 car_owner 不是强制性的，您可以考虑将其移至单独的实体：

    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

    车主的历史：

    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 可以通过触发器维护。分配汽车时，它是第一个所有者

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

    这导致以下历史变化

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

    当汽车的所有者从 o_1 更改为 o_2

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

    历史上做了如下改动：

    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);

    当汽车不再拥有所有者时，end_time 会更新以反映这一点。

    谁在时间 t_x 拥有汽车 c1？

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

    c_1 的车主有哪些？

    select owner_id 
from car_owner_history
where car_id = c_1
order by begin_time;
    • 3

  2. 对于需要历史跟踪数据更改的许多项目，我都使用了选项 2。我捕获数据的方式是在当前状态表的插入/更新上，我将获取我正在跟踪的列的当前值，并在日志表中插入一条新记录。这允许我只从一个表加载数据，并让应用程序层突出显示从一条记录到另一条记录的更改。

    • 2

  3. 我想一般一辆汽车的使用寿命大概是10到20年，所以这意味着一辆汽车可能只有非常有限的车主。

    如果这个假设是正确的，我将通过添加两列（和一个可选列）来设计 [car] 表，如下所示

    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"
);

    如果一辆汽车改变了所有权，你只需要更新OwnerEndDate，并向汽车表插入一条新记录，OwnerEndDate为空，意味着这辆车还不是“历史”，对于这条新的汽车记录，你可以更新PrevCarID列到ID列您刚刚更新其OwnerEndDate列的记录。

    这样，表 [car] 既可以是历史表，也可以是实时 OLTP 表。正如假设中提到的，对于同一辆汽车，它不应该产生太多的记录。

    这种设计的优点是简单。

    • 0 
  4. 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),
  );

    汽车表包含汽车的具体信息。这样它就可以满足任何 Biz 要求。比如保养、维修、保养等。

    这样你的第一句话是错误的。

    一辆车一次只能有一个regNum，一个shopId和一个ownerId，这就是为什么它们被嵌入到汽车表中。

    同意“一辆汽车一次只能有一个 regNum、一个 shopId 和一个 ownerId”，但不能仅出于这个原因将它们嵌入汽车表中。

    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"
);

    我不确定是否regNum 应该在 Current_car_Status或Car_Status_History

    当数据量将以百万计，并且在 Car Status 表中查询非常频繁时，您应该创建 History 表，否则您应该保留一张表。

    CARID、shopid、ownerid、regnum 可以是复合聚集索引。但您不必创建它们，因为每个索引都作为外键引用，如果将它们与各自的表连接，则将使用索引。

    Car 表可以根据需要在其他属性上建立索引。

    应用层/报告是正确的媒介，以用户友好的方式显示所有者变更历史。

    Car 表不应包含 shopid 或 Ownerid。

    • 0

相关问题