Attribute Table

An attribute table in its simplest form is a DictionaryDataStructure of name-and-value pairs. A fancier version may have a schema like:

  Table: Attributes
  -------------------
  entityRef   // foreign key to Entity table or table name
  fieldName
  fieldType   // text, numeric, date, etc. (optional)
  fieldValue

Here is a variation that allows dynamic columns for an existing table.

  Table: Attributes
  -------------------
  rowRef   // foreign key to record.
  fieldName
  fieldType   // text, numeric, date, etc. (optional)
  fieldValue


An AttributeTable is sometimes called an "EAV" table (Entity, Attribute, Value). They can be useful in cases where the user needs to add new kinds of attributes without calling or hiring a DBA or programmer. For example, in off-the-shelf applications, having the application add new columns on-the-fly may be risky and complicated. For example, the database engine used may require administrator access to add and remove columns, but the app may not be able to depend on that for a given user. An AttributeTable may allow similar flexibility without the need to add columns.


I believe AttributeTable is a pattern (or anti-pattern) that only exists because current pseudo relational sql databases make it hard for people to alter their structure, after all, any database can represent is schema as an AttributeTable.

In the sql standard the main instance of the AttributeTable pattern it is known as the InformationSchema, sadly it does not cover every kind of thing that can be inside a database, and the databases that support it only do it in a read only way, and therefore it is impossible to use it to alter the schema, you can not for example insert records in to the INFORMATION_SCHEMA.TABLES table and have a new table created as a result. In other words if the InformationSchema were really supported in a read/write way by most current pseudo relational sql databases there would be no need to have more than one instance of this pattern in each database.

I'd like to see DynamicRelational for situations where "run-time" flexibility is needed. And please, let's not start another "type war" here. -t

I have no desire to start a war, I am not against DynamicRelational, but I am also not pro DynamicRelational, what I would like is to have a database that I can configure and make as strict or as dynamical as I need, or even mix paradigms if that makes it easier for me to implement the requirements of the user. I want to follow the MiddleWay.

What's proposed in DynamicRelational does this, or at least allows such. For example, you could add a constraint to a table indicating that dynamic (new) columns are no longer allowed. This allows incremental lock-down. Perhaps I should give more examples there.

I feel that incremental lock-down is only superficially mentioned in DynamicRelational...

I fleshed out some of the examples. However, if you feel that heavy enforcement is important for your niche or design style, then DynamicRelational is probably not for you.
See Also: SparseColumns, MultiParadigmDatabase, ControlTable, DataDictionary, GenericDataModel, DynamicRelational

EditText of this page (last edited September 2, 2013) or FindPage with title or text search