Reputation: 365
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:
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:
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