"Variant" values structure and their use for multiple columns

Question

2017/07/25 note: This was a quite complex question, re-reading it 4 years after I posed it I believe it has still some value, but it's sure hard to understand. You probably shouldn't bother if you're not facing a similar problem. If you see a way to enhance it please do edit it.
For what is worth I think I ended up using my solution. The project this was part of was eventually aborted, though, so the thing was never tested in real conditions.
In retrospect, as far as I can recall, it would have probably been better to use other, simpler, solutions, maybe not using a relational database to store the information; it was one of my first experiences in serious database design.

---

note to the 2013/04/15 edit:

The question originally was only about the opportunity of using a single "variant" structure for multiple columns, but all initial comments asked clarifications about the actual structure itself, something I hoped to eventually discuss later, it is probably hard to separate the two matters so now I want to discuss both the best implementation of a "variant values" structure and the convenience to use them for more than one column.

I reworked the old question because it would get too messy otherwise. Previous readers do not need to re-read the question, although that may clarify some things.

---

Reasons

Sometimes you have columns that legitimately can have values of any simple type at all the times but your DBMS does not support a variant type.

Probably this can occur only for metadata or other exotic uses of databases, let's not discuss about the appropriateness of these things, assume there can be legitimate reasons for them.

Just note that I'm meaning to use the system for a limited set of columns for complex functionalities, not as a convenience for OTLT or such things.

You can find more information on my present specific case in the comments to this question and to this answer.

Clarifications

I want a column to be able to have one single value for each row, and I don't want ways to restrict the types accepted by single rows - the value for every row can legitimately come from any of the types supported by the structure at all the times.

There are cases when you want for example another column to determine the type of the valid values, but in this question I'm considering only the unrestricted case.

Note: most of the times the information you store cannot truly accept any type, for example if you store metadata about another database where each row is about a column from that database that column has obviously a defined type, but if you don't need the type information for other uses it is not necessary to store a separate "Type" column, unless you assigned different security permissions it would be exactly equivalent to set a value in a "Type" column or to directly choose a value from one of the supported types.

Structure example / proposal

This structure uses a table for the values' IDs (Values) that would be referenced by the column / columns. The various Values_ tables contain the actual values; I here put some tables, if you used the structure for many columns you might put more tables, one advantage of using one structure per column is that you may need less tables for some columns, for example for some you may be sure they will never need fractional numbers of a great precision.

Note: if you used a structure for multiple columns you would always only allow types all valid for all of the columns, the minor number of tables in the single structure per table I just mentioned would only be due to including only the expected types, but it wouldn't be a catastrophe to set by mistake one of the less-expected types in the single-structure case.
Ok if this is not clear it is not important, don't mind.

The referential constraints to Values.Type are there only to ensure that only one actual value can be assigned to each Values.ID. I was not sure about referencing columns that does not constitute a primary key but it seems to be ok, I saw it used in several answers on this site.

Values_Null indicates Null "values", irrespective of the type; sometimes you might need this - and you wouldn't care of what type the column containing the Null actually had - we only want to indicate values here; this table would actually normally contain only one or zero rows if you reuse the values - more on this "reusing the values" under "Use" below.

CREATE TABLE ValueTypes (
ID INT PRIMARY KEY, 
Name VARCHAR (30)
)

INSERT INTO ValueTypes (ID, Name) VALUES (1, 'Int')
INSERT INTO ValueTypes (ID, Name) VALUES (2, 'VarChar255')
INSERT INTO ValueTypes (ID, Name) VALUES (3, 'Money')
INSERT INTO ValueTypes (ID, Name) VALUES (4, 'Boolean')
INSERT INTO ValueTypes (ID, Name) VALUES (5, 'Null')

CREATE TABLE Values (
ID INT PRIMARY KEY, 
Type INT NOT NULL,
Notes CHAR LARGE OBJECT, 
FOREIGN KEY (Type) REFERENCES ValueTypes (ID)
)

CREATE TABLE Values_Int (
ValueID INT PRIMARY KEY
Type INT CHECK (Type=1),
Value INT,
FOREIGN KEY (ValueID, Type) REFERENCES Values (ID, Type)
)

CREATE TABLE Values_VarChar255 (
ValueID INT PRIMARY KEY,
Type INT CHECK (Type=2),
Value VARCHAR (255),
FOREIGN KEY (ValueID, Type) REFERENCES Values (ID, Type)
)

CREATE TABLE Values_Money (
ValueID INT PRIMARY KEY
Type INT CHECK (Type=3),
Value DECIMAL (15,4),
FOREIGN KEY (ValueID, Type) REFERENCES Values (ID, Type)
)

CREATE TABLE Values_Boolean (
ValueID INT PRIMARY KEY
Type INT CHECK (Type=4),
Value BOOLEAN, 
FOREIGN KEY (ValueID, Type) REFERENCES Values (ID, Type)
)

CREATE TABLE Values_Null (
ValueID INT PRIMARY KEY, 
Type INT CHECK (Type=5),
FOREIGN KEY (ValueID, Type) REFERENCES Values (ID, Type)
)

Alternative "variant" structures and reasons for my example

The alternative "variant" solutions I can think of are:

• The most trivial of using a simple textual column for any type. I'm ruling out this because of the high chances of formats mistake, in addition to space waste and poor performance.
• Putting all the columns for the various different types in a single table. This would mean a lot of Nulls, thus waste of space, but maybe it would be acceptable? Colin 't Hart below advocated this.
• Making the single Values_ tables reference directly the table where the "variant" column is contained. This would mean of course one set of tables for each of these columns, confusion if more than one column is needed in one table, and most of all impossibility to enforce one single value per row, unless you put also a "Type" column for each of these columns.

Use

I would delete old values when they're not referenced anymore, thus forbid their direct external use in queries, and when needing values already used allow at user will to either reference the existing record or insert a duplicate value (thus in general the values should never change, only be deleted - although maybe a functionality to give a precise meaning to a record, using appropriate additional flag columns, could be useful, though probably confusing).

Questions

1. What could be the best "variant" structure in general?

2. Would it be better to use one structure per column or one for all colums (meaning all with the same set of accepted values)?

   The downsides I can see of using one single structure for multiple colums is the need to search among more values and possibly the need for one more table in the middle and thus one more join - although this table can be convenient also when using one structure per column.
   I would like to get ideas about at what point a lot of (indexed) values in a table start to slow their search significantly and make division in more tables significantly convenient.

   The downside I can see of one structure per column is a lot more tables, meaning more confusion - maybe too much.

Thanks

Thank you if you read all this

Answer 1

If you really need to do this (a la Windows Registry values) I would probably try to keep it simple and keep it to one table something like this (untested pseudo-DDL):

create table xyz (
  id int primary key,
  name varchar(30) not null,
  type varchar(10) not null check (type in ('Integer','Date','Money')),
  integer_value integer,
  date_value date,
  money_value money,
  check (
    type = 'Integer' and integer_value is not null and date_value is null and money_value is null
    or
    type = 'Date' and date_value is not null and integer_value is null and money_value is null
    or
    type = 'Money' and money_value is not null and integer_value is null and date_value is null
  )
)

Extend or adjust for data types as needed.

Remove <datatype>_value is not null for each data type if you wish to allow null values.

The only complexity is the constraint to enforce that the appropriate <datatype>_value column is used.

If values are mandatory, then the check constraint can be written as

check (
  (type = 'Integer') = (integer_value is not null) and
  (type = 'Date') = (date_value is not null) and
  (type = 'Money') = (money_value is not null)
)

which is a bit shorter but not as intuitive.

Answer 2

Do you know all possible metadata "keys" in advance? If so, then I would go down a different route entirely and model this is a single table -- or one per subsystem/area -- with each key as a column. This is the approach I use when developing database-centric applications.

create table xyz_parameters (
    menu_colour          varchar2(10) not null check (menu_colour in ('Blue','Green','Yellow')),
    abcsys_cutover_date  date         not null check (abcsys_cutover_date >= date '2013-01-01'),
    ghi_additional_value integer               check (ghi_additional_value between 1 and 10),
    door_location        varchar2(40)
)

etc

Some values are mandatory, others are optional. Check constraints are used to further specify valid values for parameters.

For this to work you need to be Agile in order to be able to add and drop columns as needed. But this is how we do it.