w_n
w_n

Reputation: 365

Historicized entity attributes with common end date

Suppose I want to create an EAV-style set of tables with the attributes having a validity date.

The normalized way to do that is to have a single date column (let's call it valid_by), since having two valid_from and valid_to columns would make update anomalies possible.

create table entity (
    entity_pk text primary key
);

create table entity_info (
    entity_pk text references entity.entity_pk,
    attribute_code text,
    valid_by date,
    info text,
    primary key (entity_pk, attribute_code, valid_by)
);

Thing is, these infos might not be valid indefinitely, since the entity itself might become invalid at some point in time.

How do I model this correctly in the relational way?

I've come up with two approaches, both of which have downsides that make me question them.

First approach: Have a valid_to column in the entity.

create table entity (
    entity_pk text primary key,
    valid_to date
);

create table entity_info (
    entity_pk text references entity.entity_pk,
    attribute_code text,
    valid_by date,
    info text,
    primary key (entity_pk, attribute_code, valid_by)
);

This way, I have a single place where I can set the end date for the whole entity. The downsides are:

  1. I have to join to the entity table on every query and check if the entity is still valid
  2. I can create entity infos with a valid_by date past the valid_to date of the entity. To prevent this I'd have to check the entity's end date on every insert and update of infos.

Second approach: Create tombstone infos.

create table entity (
    entity_pk text primary key
);

create table entity_info (
    entity_pk text references entity.entity_pk,
    attribute_code text,
    valid_by date,
    info text nullable,
    primary key (entity_pk, attribute_code, valid_by)
);

This way, when I want to end an entity I insert a special record that marks the end of the attributes' validity period, most likely by setting the info to null. All queries can now simply use the info table without joining to the entity table. The downside is:

  1. I need to insert the tombstone for every attribute of the entity, duplicating that information and allowing for update anomalies

Is there another way to model an entity-wide end date? Which one would be preferred from a normalization standpoint?

Edit: Another approach would be to have a dedicated tombstone attribute, but that seems to have the same ups and downs as approach 1 with slightly worse handling.

Upvotes: 0

Views: 26

Answers (0)

Related Questions