6 ms·

> there's one table with the columns "rowid, columnname, value" Yeah, that's known as "entity-attribute-value model" (or just "EAV") and is considered an anti-

> there's one table with the columns "rowid, columnname, value" Yeah, that's known as "entity-attribute-value model" (or just "EAV") and is considered an anti-pattern unless you actually need the flexibility it provides (which is fairly rare).

da_chicken 5y ago
Yup. We run a system that uses this pattern for user-defined data fields. It works, but not without difficulty. It's very difficult to validate with business rules because you can't eliminate nulls and the "value" field is forced to be a character field. The system does have type checking enforced by the application, but multiple applications access the data.
- branko_d 5y ago
  > the "value" field is forced to be a character field It doesn't have to be. We use this (under SQL Server) for our simple "custom attributes" mechanism (some irrelevant details omitted): CREATE TABLE private_data.ATTRIBUTE_VERSION ( OBJECT_ID bigint, ATTRIBUTE_NAME nvarchar(255), ATTRIBUTE_GEN bigint, IS_TRASH bit NOT NULL, VALUE_BOOL bit, VALUE_INT bigint, VALUE_DECIMAL decimal(38, 9), VALUE_DATETIME datetime2, VALUE_STRING nvarchar(255), ATTRIBUTE_TYPE AS private_attributes.ATTRIBUTE_VALUE_TO_TYPE( VALUE_BOOL, VALUE_INT, VALUE_DECIMAL, VALUE_DATETIME, VALUE_STRING ), CONSTRAINT ATTRIBUTE_VERSION_PK PRIMARY KEY (OBJECT_ID, ATTRIBUTE_NAME, ATTRIBUTE_GEN), CONSTRAINT ATTRIBUTE_VERSION_FK1 FOREIGN KEY (OBJECT_ID, ATTRIBUTE_NAME) REFERENCES private_data.ATTRIBUTE, CONSTRAINT ATTRIBUTE_VERSION_FK2 FOREIGN KEY (ATTRIBUTE_GEN) REFERENCES private_data.GENERATION, CONSTRAINT ATTRIBUTE_VERSION_C1 CHECK ( -- At most one of the various value types can be non-NULL. private_attributes.ATTRIBUTE_VALUE_CHECK( VALUE_BOOL, VALUE_INT, VALUE_DECIMAL, VALUE_DATETIME, VALUE_STRING ) = 1 ) ); Since SQL Server encodes NULLs in a bitmap, having a bunch of NULLs for every non-NULL takes almost no extra space at all. But even on a "wasteful" database such as Oracle, this shouldn't add more than an extra byte per NULL. > The system does have type checking enforced by the application, but multiple applications access the data. Traditionally, this is solved by funneling all applications through a public API of stored procedures and views/functions which enforce the desired business logic, but I understand this approach has fallen out of favor in recent years (decades?).
  - da_chicken 5y ago
    That's really not an improvement. This complex enough that you're going to run into problems when multiple systems use the same database. I can imagine the fun that comes when another system wedges a field because it used the wrong type. That's the same problem we already have with a single text column. This is way more complex and still has the same problems.

da_chicken 5y ago

Yup. We run a system that uses this pattern for user-defined data fields. It works, but not without difficulty. It's very difficult to validate with business rules because you can't eliminate nulls and the "value" field is forced to be a character field. The system does have type checking enforced by the application, but multiple applications access the data.

branko_d 5y ago
> the "value" field is forced to be a character field It doesn't have to be. We use this (under SQL Server) for our simple "custom attributes" mechanism (some irrelevant details omitted): CREATE TABLE private_data.ATTRIBUTE_VERSION ( OBJECT_ID bigint, ATTRIBUTE_NAME nvarchar(255), ATTRIBUTE_GEN bigint, IS_TRASH bit NOT NULL, VALUE_BOOL bit, VALUE_INT bigint, VALUE_DECIMAL decimal(38, 9), VALUE_DATETIME datetime2, VALUE_STRING nvarchar(255), ATTRIBUTE_TYPE AS private_attributes.ATTRIBUTE_VALUE_TO_TYPE( VALUE_BOOL, VALUE_INT, VALUE_DECIMAL, VALUE_DATETIME, VALUE_STRING ), CONSTRAINT ATTRIBUTE_VERSION_PK PRIMARY KEY (OBJECT_ID, ATTRIBUTE_NAME, ATTRIBUTE_GEN), CONSTRAINT ATTRIBUTE_VERSION_FK1 FOREIGN KEY (OBJECT_ID, ATTRIBUTE_NAME) REFERENCES private_data.ATTRIBUTE, CONSTRAINT ATTRIBUTE_VERSION_FK2 FOREIGN KEY (ATTRIBUTE_GEN) REFERENCES private_data.GENERATION, CONSTRAINT ATTRIBUTE_VERSION_C1 CHECK ( -- At most one of the various value types can be non-NULL. private_attributes.ATTRIBUTE_VALUE_CHECK( VALUE_BOOL, VALUE_INT, VALUE_DECIMAL, VALUE_DATETIME, VALUE_STRING ) = 1 ) ); Since SQL Server encodes NULLs in a bitmap, having a bunch of NULLs for every non-NULL takes almost no extra space at all. But even on a "wasteful" database such as Oracle, this shouldn't add more than an extra byte per NULL. > The system does have type checking enforced by the application, but multiple applications access the data. Traditionally, this is solved by funneling all applications through a public API of stored procedures and views/functions which enforce the desired business logic, but I understand this approach has fallen out of favor in recent years (decades?).
- da_chicken 5y ago
  That's really not an improvement. This complex enough that you're going to run into problems when multiple systems use the same database. I can imagine the fun that comes when another system wedges a field because it used the wrong type. That's the same problem we already have with a single text column. This is way more complex and still has the same problems.