We are designing a database and one of the analysts has suggested that we have 5 or 6 generic fields in each table for user defined fields. He is suggesting a numeric, a date, a text, etc, etc. Then if the user ever wants a new field they are already in the table and the application can make use of them.

What are the pros and cons of doing this? Any best design practices? I have seen this in commercial applications but I am not sure of any issues with doing this.

-- Garth Belanger (garth.belanger@spmc.gov.sk.ca), August 20, 2001

Answers

This hits me as a realllly weird idea. To me, it says that your data model was not finished, so you have a block on the diagram that reads "here a miracle occurs". You will not be able to maintain the schema in your product because no two customers will use these "wild columns" for the same purpose.

This idea is a hang over from the file system days. You have a guy hwo does not know the differences between (tables, rows, columns) and (fields, records, tables).

We lived in trees, ate our children and programmed in COBOL (which is spelt Cobol these days).

In 3GL languages, fields (NOT columns) are defined by the host program. That is why "READ(a, b, c) FROM File #1" is not the same as "READ(c, a, b) FROM File #1" while "SELECT a, b, c FROM Table_1;" gives us the same data as "SELECT c, a, b FROM Table_1;", allbeit in a different order when you display it in a host program.

One of the things you did in Cobol was declare parts of storage to be FILLER, a reserved word that said ignore what is in this physically contigous storage area of the physical storage media you are reading.

FILLER might have data used by another application which had different READ and WRITE statements. Or it might really be unused space. Adding new fields (NOT columns) to a record (NOT rows) on a magnetic tape file (NOT tables) was a screaming pain. So we left a little bit of free space on the end of each record for future growth.

Remember that records can vary in size, so we might allow space in the personnel record for the day that an employee has ten kids. In SQL you would declare a Dependents table and join it to the personnel table instead.

--CELKO--

-- Joe Celko (71062.1056@compuserve.com), October 01, 2001.