-2

A common pattern in database design is to append dynamic columns to a table with a design like so:

create table MasterTable
(
    Id int not null primary key 
    , Name nvarchar(64) not null unique
    , SomeColumn2 int not null
)

create table MasterTableAdditionalColumn
(
    Id int not null primary key 
    , ColumnName nvarchar(64) not null unique   
)

create table MasterTableAdditionalColumnData
(
    Id int not null primary key
    , MasterTableId int not null foreign key references MasterTable(Id)
    , MasterTableAdditionalColumnId int not null foreign key references MasterTableAdditionalColumn(Id)
    , Value nvarchar(1024) not null
)

create unique index UK_MasterTableAdditionalColumnData_Key
    on MasterTableAdditionalColumnData 
    (
        MasterTableId
        , MasterTableAdditionalColumnId
    )

i.e. A design whereby we can effectively add a new nvarchar(1024) column to our MasterTable at any time by creating a new entry in MasterTableAdditionalColumn for the column name; and can populate this columns values by creating entries in MasterTableAdditionalColumnData with the appropriate column's Id (MasterTableAdditionalColumnId) and the related master record's Id (MasterTableId).

Is there a name for this pattern / type of table? I've always used names like dynamic columns or extension table, but haven't had any luck finding those terms or anything official on Google.


Update

An example of what I'm referring to can be seen here: http://sqlfiddle.com/#!18/0a911/2

i.e. columns Surname and JobTitle in the result set get their values from a list of name-value pairs for each MasterTable record; i.e. from the MasterTableAdditionalColumnData table.

I'm wondering what the term for a table such as MasterTableAdditionalColumnData; or this approach to designing the data model to allow columns to be defined at run time is.

  • 1
    This is common? Can you provide some evidence of that claim? I'm not a DB guy per se but I'm not familiar with this. I'm not actually sure exactly what you are saying. Are you actually adding columns or are you talking about static structure that is used like an equivalent dynamic table structure? – JimmyJames Apr 19 '18 at 16:04
  • @JimmyJames without knowing the term it's hard to search for, but I've seen this approach used in a number of off-the-shelf products as well as having used it myself. Here's an SO post where someone's used a similar approach: stackoverflow.com/questions/7448453/… – JohnLBevan Apr 19 '18 at 16:09
  • I have a sense that you are talking about a normalization but I'm still not sure exactly what you are describing. – JimmyJames Apr 19 '18 at 16:20
  • This doesn't physically add columns to the table; rather it allows "virtual columns". i.e. See this example: sqlfiddle.com/#!18/0a911/2 – JohnLBevan Apr 19 '18 at 16:24
  • 1
    "User-Defined Columns." I know you're trying to find a good Google term, but that's all this is. See martinfowler.com/bliki/UserDefinedField.html – Robert Harvey Apr 19 '18 at 16:32
4

Entity-Attribute-Value (EAV) Model seems to be the term I was looking for. Here's the Wikipedia Article on the topic.

It seems most people advise against this pattern, but as with everything it's about context / weighing up pros and cons for the scenario being used.

Here's a balanced blog post with some additional discussion around the pattern: https://sqlblog.org/2009/11/19/what-is-so-bad-about-eav-anyway

Attribute Table is an equally valid answer. Thanks to @RobertHarvey for pointing me in this direction in the comments.

Here's a blog post by Martin Fowler on this, and a related wiki article.

  • 1
    The edit to your question doesn't really describe EAV. EAV is used when there can be arbitrarily many different attributes, but only a few of those attributes (arbitrarily chosen) will have a value for a given entity. The Wikipedia article explains this using a physician example, where a patient can have a few arbitrarily-chosen symptoms from a large list of possible symptoms. None of this really has anything to do with adding columns to a table; it's essentially a collection of key-value pairs. – Robert Harvey Apr 19 '18 at 17:55
  • @RobertHarvey agreed that the usage I described differs slightly; i.e. sparse/one-off columns per row vs custom columns; but the term for the implementation was what I was after (I admit that term may change depending on context; but I had the implementation in mind rather than the problem to be solved when asking). – JohnLBevan Apr 19 '18 at 18:13

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.