1

A few years ago I've stumbled upon a database design approach, but I can't find any information on this for the love of my life.

Its main feature was normalizing tables in a way where every "property" of entities was stored in their own table. E.g. a user entity, normally stored in a single table with an id, email and other fields, was split into tables for each field. So users' email address would have been stored in a table with a field to contain a user's current email address and a datetime (when this email address was set or last updated) with a user id or similar. The model would track every change to a user's email address by creating new records in this table with updated timestamps. (Not sure if this made sense.)

This approach was documented online and it had an online tool to visualize such a model (as a graph) and it had a way to export the visually created model as an SQL schema (with full support for MSSQL).

Anyone have an idea about the name of this design approach?

Edit.: I remember that the author of this approach (or the visual modeller that is/was available online) mentioned that the tool was able to produce database schema (or "optimized JOINs") for MSSQL since it has (had) the ability to efficiently to "prune tables".

Edit.: By "prune tables" I meant "eliminate tables".

  • 1
    Every field was it's own value in it's own table? That's the EAV model, I believe. – Jacob H Oct 21 at 16:05
  • 1
    @JacobH EAV has a single ATTRIBUTE_VALUE table and all values of all entity types populate that single table, including primary keys. It would have a schema something like(Entity_Id, Attribute_Id, Value). – Michael Green Oct 22 at 4:48
2

Your description could be of SQL Columnstore indexes

This index uses column-based data storage and query processing

stored in a column-wise data format

To reduce fragmentation of the column segments and improve performance, the columnstore index might store some data temporarily into a clustered index called a deltastore and a btree list of IDs for deleted rows. The deltastore operations are handled behind the scenes. To return the correct query results, the clustered columnstore index combines query results from both the columnstore and the deltastore.

2

a user entity .. was split into tables for each field

That's 6NF. I've never ever seen a DB implemented to this level of normalization. I shudder to think what the queries would look like. The write amplification must be quite huge, too, given that every table will repeat the PK and index it. In today's world I'd much rather use column-oriented storage, let the DBMS handle the decomposition, and enjoy whatever compression it can apply.

The model would track every change .. by creating new records .. with updated timestamps.

That's temporal tables. Some DBMS provide syntax to declare this in the DDL. It's not difficult to implement manually. Each table to be tracked is given timestamps to show when the value(s) in that row were considered the current value. To use your 6NF email example, the table would be

User_id,
Email,
Applicable_from,
Applicable_to

Say a new user was created at midday on 1st January. The table would then contain

1   person@example.com   2019-01-01 12:00:00.00   NULL

The NULL shows this row is applicable indefinitely. On 2nd Feb that user changed email address. The table then holds

1   person@example.com   2019-01-01 12:00:00.00   2019-02-02 13:09:17.99
1   me@example.com       2019-02-02 13:09:17.99   NULL

Note that the end datetime of the first row exactly matches the start datetime of the new row. And finally in March the account is deleted. After the deletion the table holds

1   person@example.com   2019-01-01 12:00:00.00   2019-02-02 13:09:17.99
1   me@example.com       2019-02-02 13:09:17.99   2019-03-17 09:00:00.00

The second (i.e. later) rows is closed. The user is logically deleted from the system although the rows are retained. In this way audit and "time travel" queries are possible.

When implemented manually every query that touches such temporal tables must include the "from" and / or "to" columns in the predicate.

Time series databases are optimized specifically for this type of data.

1

You might want to read up on "Temporal Database Design".

One aspect of the design you outline reminds me of temporal databases. It's the aspect that every fact is tagged with a time interval during which that fact is valid.

The rest of the design, the over normalization, doesn't strike me as necessary for a temporal database, but I could be wrong about that.

1

Based on inputs I was able to find what I was looking for.

Anchor Modelling (Wikipedia)

The mention of EVA and 6NF gave it away, I just need to search for "entry value model 6nf" and Anchor Modelling was amongst the results.

Thanks for everyone!

0

I don't know how that is called, but unless you have a database that uses a column store, that design is horrible:

  • Retrieving an entity has to select from many tables.

  • Inserting an entity has to modify many tables and all indexes on them.

  • Either your queries become complicated, or you have to run quite a lot of queries.

With a traditional relational database, it is hard to come up with a worse data model.

  • Sounds like the model OP is describing is a data warehouse with Kimball methodology. – Sean Brookins Oct 21 at 20:45

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.