database-design - 在PostgreSQL中实现Type-2缓慢变化的维度

我计划在PostgreSQL中实现Type-2 SCD。
scd的缺点是，如果像通常看到的那样使用，就不能让外键引用thos表。换句话说，我经常看到在应用程序代码中处理引用完整性。这对我来说是一种糟糕的做法，因为它可以直接在数据库中完成。添加一些触发器甚至可以向应用程序编码人员隐藏这个实现细节。
我想出了以下方案。这些还好吗？
一对多

--
-- One-to-Many
--
BEGIN;

   CREATE TABLE document(
      id serial not null,
      revision integer not null default 1,
      title varchar(30),
      primary key (id, revision)
   );

   CREATE TABLE page(
      id serial not null,
      title varchar(30),
      document_id integer not null,
      document_revision integer not null,
      foreign key (document_id, document_revision) references document(id, revision)
   );


   -- Insert the first revision
   INSERT INTO document (title) VALUES ('my first document');
   INSERT INTO page (title, document_id, document_revision) VALUES ('my first page', 1, 1);

   -- DEBUG: display
   SELECT * FROM document d inner join page p ON ( d.id = p.document_id and d.revision = p.document_revision );

   -- "update" the document, by inserting a new revision
   INSERT INTO document (id, revision, title) VALUES (1, 2, 'my first document, edited');

   -- update the references
   UPDATE page SET document_revision = 2 WHERE document_id = 1;

   -- DEBUG: display
   SELECT * FROM document d inner join page p ON ( d.id = p.document_id and d.revision = p.document_revision );

ROLLBACK;

多对一

--
-- Many-to-One
--
BEGIN;

   CREATE TABLE page(
      id serial not null primary key,
      title varchar(30)
   );

   CREATE TABLE document(
      id serial not null,
      revision integer not null default 1,
      title varchar(30),
      page_id integer references page(id),
      primary key (id, revision)
   );

   -- Insert initial revision
   INSERT INTO page (title) VALUES ('my first page');
   INSERT INTO document (title, page_id) VALUES ('my first document', 1);
   INSERT INTO document (title, page_id) VALUES ('my second document', 1);

   -- DEBUG: display
   SELECT * FROM page p inner join document d on (p.id = d.page_id);

   -- destroy the link "from" the old revision
   UPDATE document SET page_id = NULL WHERE id=1;

   -- Add a new revision, referencing the page
   INSERT INTO document ( id, revision, title, page_id ) VALUES ( 1, 2, 'My First Document, edited', 1 );

   -- DEBUG: display
   SELECT * FROM page p inner join document d on (p.id = d.page_id);
   SELECT * FROM document;

ROLLBACK;

多对多

--
-- Many-to-Many
--
BEGIN;
   CREATE TABLE page(
      id serial not null primary key,
      title varchar(30)
   );

   CREATE TABLE document(
      id serial not null,
      revision integer not null default 1,
      title varchar(30),
      primary key (id, revision)
   );

   CREATE TABLE page_contains_document(
      page_id integer not null references page(id),
      document_id integer not null,
      document_revision integer not null,
      foreign key (document_id, document_revision) references document( id, revision )
   );

   -- Insert initial revision
   INSERT INTO page (title) VALUES ('My First page');
   INSERT INTO document (title) VALUES ('My Fist Document');
   INSERT INTO page_contains_document (page_id, document_id, document_revision) VALUES (1, 1, 1);

   -- DEBUG: display
   SELECT p.title, d.title, d.revision FROM page p INNER JOIN page_contains_document pcd ON (p.id = pcd.page_id) INNER JOIN document d ON (d.id = pcd.document_id and d.revision = pcd.document_revision);

   -- Add a new document revision
   INSERT INTO document (id, revision, title) VALUES (1, 2, 'My Fist Document, edited');

   -- update the reference
   UPDATE page_contains_document SET document_revision=2 WHERE document_id=1;

   -- DEBUG: display
   SELECT p.title, d.title, d.revision FROM page p INNER JOIN page_contains_document pcd ON (p.id = pcd.page_id) INNER JOIN document d ON (d.id = pcd.document_id and d.revision = pcd.document_revision);

ROLLBACK;

最佳答案

好 啊。我认为我们需要澄清一些重要的误解，为什么我们做2型SCD。
它应该把所有的数据放在一个表中，用日期括起来（不是修订号！）.
所以，你可以：

   id   ,  name    ,  valid_from, valid_to
  1111  , MyBook   , '2009-03-01', '9999-12-31'

After an update:
  1111  , Mybook   , '2009-03-01', '2009-06-20'
  1111  , Mybook   , '2009-06-21', '9999-12-31'

在“页面”数据库中应该存在一个具有有效和有效日期的类似结构。
关键是，现在您可以通过以下方法获取最新版本：

select * from books where valid_to = '9999-12-31'

或者得到4月1日有效的版本

select * from books where valid_to >= '2009-04-01' and valid_from <= '2009-04-01'

在页面结构中，您只需要存储更新的页面。你不需要每次修订都需要一份所有页面的新副本。

关于database-design - 在PostgreSQL中实现Type-2缓慢变化的维度，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/1620791/