Fields As Records

I am not sure if this is a pattern or an antipattern.

Suppose you have a structure like this:

 TableX
 K A B
 -----
 1 a x
 2 b y
 3 c z

(K is primary key)

When one uses FieldsAsRecords, it is stored in a table like this:

 TableX
 F V
 ---
 K 1
 A a
 B x
 K 2
 A b
 B y
 K 3
 A c
 B z

(F: Field, V:Value)

Thus, if one uses a DataDictionary for fields definition, adding a field means just adding a record and not modifying the table definition

PLEASE, CORRECT THIS TEXT IF YOU FIND IT NECESSARY. THIS FIRST VERSION IS NOT WRITTEN BY AN ENGLISH SPEAKER. THANKS.

I've seen this (almost) used before. It's an AntiPattern if you ever need to store more than one such structure in the table. Instead of being able to use normal RelationalAlgebra operators, you've got to loop through a much-larger ResultSet within the application and coalesce the structures. You're essentially rotating and grouping records within app code instead of letting the database do it for you. This is not a net win for performance nor maintainability.

It's really tempting to do this, just so you never need to ALTER TABLE. But you lose all semblance of the RelationalModel, and SELECTs become much harder to write. Try doing a join on records like this: there's nothing to join on. When you consider all the extra application code you have to write to get this to work, you end up much worse off than just altering the table structure. -- JonathanTang

Yeah, I think it's always an AntiPattern. It's very important to split this into two tables, with the first table acting as schema for the second table. It's on Codd's list of the 12/50/380/xyz (the number kept growing) definitional criteria for a "true" RDBMS. Fortunately, that good practice has also caught on as the most common practice. -- dm

When you see a need for this, what one often really wants is "dynamic relational", as described under MultiParadigmDatabase. However, those don't really exist in practice to date. Sometimes things such as account classifications and the like are made into columns when it seems like rows might be more change-friendly. It is a tough call.

This is right at the other end from ColumnsAsDimensions?.

This is most commonly seen when MicrosoftExcel is used as a database. The first few columns make sense (like Name, Phone, Cell, Fax, Birthdate, Street, City, State, Zip) and then one discovers that there are more phones, secondary addresses, multiples of various things. Then, of course, if you've left three columns for "children's names" you get a family with five kids. As more dimensional material shows up, columns are added out to the right.

And then someone wants it converted to a real database, but one of the required reports must look exactly like the stinking spreadsheet, since that's what they were "used to" in the old system.

-- GarryHamilton