An experienced software engineer described a database design pattern to me in which there are three kinds of tables: transactional tables, associative tables, and data definition/taxonomy tables.

As he described it, a transactional table might store information about a user, product, etc. For example, the USER table might have columns id, lastname, firstname, username, password, etc. The PRODUCT table might have columns id, description, price, etc.

An associative table is used to create many-to-many relationships between other tables. An example would be a table for keeping track of items in each user's shopping cart. It might have columns product_id and user_id.

A data definition table contains information about the definition of columns in other tables. For example, there might be a table to that lists password hashing algorithms used over the lifetime of the database and the USER table might have an additional column like password_definition that points to this table. In this way, we don't end up adding columns like password_md5, password_sha256, password_hmac, etc.

My example is contrived just to help describe this pattern. I'm not looking for advice about the example, but information about the design pattern illustrated and specifically about using a table to describe data in another table.

Does this kind of database design have a name? Are there better terms for these types of tables? Are there established best practices for when it should or should not be used?

  • It could be a specialized (or less generic) form of Entity-Attribute-Value. – FrustratedWithFormsDesigner Mar 2 '17 at 22:16
  • 1
    An ER diagram, however crude, would help better identify the pattern. You can even jot it in a napkin and post a picture of it. This is one case where the proverbial picture is worth a thousand words (or a wall of text). – Tulains Córdova Mar 2 '17 at 22:37

The transaction tables and the association tables are a typical implementation of an entity-relationship model.

The tables that describe data in other tables contain metadata, i.e. data on data. This is less common. Usually when speaking of metadata, one first thinks of information that the database manages. But in your design you extend it with your own metadata that makes the full thing more flexible.

This design pattern is called metadata mapping.

  • Your answer is mostly fine, but when Fowler writes "metadata mapping", IMHO he is talking about object-relational mapping only, which is a much more restricted context than the case described by the OP. – Doc Brown Mar 3 '17 at 6:43
  • @DocBrown Fowler indeed describes "metadata mapping" in the context of object relational mapping. His graphical model shows a link between object properties and table columns. So you are right. But his summary description is "Holds details about object relational mapping". So one could argue that if content of the object is flexible and defined by the metadata in the database, we are still in a kind of metadata mapping. In fact, Fowler even mentions the possibility of using reflection in the mapping (just that he reads the attributes in a file instead of a database) – Christophe Mar 3 '17 at 19:48
  • and suggest to use of Layer supertype to handle the common behavior across the different mappings. – Christophe Mar 3 '17 at 19:51

You got some answers pertaining to object relational mapping. I believe what you describe is just a so called data dictionary. https://en.m.wikipedia.org/wiki/Data_dictionary

Not the answer you're looking for? Browse other questions tagged or ask your own question.