Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

note to the 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

share|improve this question
    
I'm confused by your wording -- are you saying that, for example, a boolean value and a money value can exist for a single ValueID at the same time? Or is it always a single type of value for a given ValueID? –  Jon Seigel Apr 11 '13 at 17:38
    
I'm confused by what you're trying to do too. Do you have a single column which is used to store numbers, dates, etc. and you're trying to enforce the datatype using referential integrity? If so, this is a terrible idea. –  Chris Saxon Apr 11 '13 at 20:08
    
@JonSeigel No the intention is to allow only one type at a time. Of course this is not enforced by the schema I posted, but it was only an example, a way to enforce it is in <dba.stackexchange.com/a/5503>; –  gab Apr 15 '13 at 7:47
    
@ChrisSaxon Exactly what is a terrible idea? I need to store some values that can be taken from several data types, even though they are about the same single thing, so the alternatives are basically either to put several columns in the same table with a lot of nulls and impossibility to enforce unique constraints, use text fields at all the time with high risk of errors and inefficiency or store the values for each data type in a different table. (continues under) –  gab Apr 15 '13 at 8:07
    
@ChrisSaxon (continuation) I know that a column that can store different data types sounds (and usually is) wrong but I am pretty sure that it is right for what I have to do. These columns will not be used by queries in general, they are about "external" information that is used by the application (and I'm sure I want to put it in a database), I would just like to do it in the "least incorrect" way. –  gab Apr 15 '13 at 8:08

2 Answers 2

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.

share|improve this answer
    
Ok thank you, this is one answer, in my original question I intended to postpone the discussion of the actual structure and focus on the opportunity of having multiple columns using this structure, but it probably makes little sense and is better to discuss the two things together. So why would you choose this structure? I usually hear recommendations to split the table if there are to be many null cells. Just for the sake of simplicity, to avoid too many tables? –  gab Apr 15 '13 at 13:20
    
The real problem with nulls is that it forces more interpretation of data which results in A) increase in code and complexity to cater for the different cases, and B) the resulting reduced value of the data for the business using it. In the case here all columns that are really used are not null, enforced by the check constraint. And, yes, I prefer to avoid too many tables for this pattern. –  Colin 't Hart Apr 15 '13 at 13:36
    
If this is a metadata storage solution, why will there be multiple columns with the same variant structure? –  Colin 't Hart Apr 15 '13 at 13:37
    
@ColintHart I don't know if there is a single "metadata storage" pattern, I'm storing various informations for a set of applications, I have currently 4 individual informations for which I initially put multiple columns in each table, similiarly to your solution, and am now evaluating other approaches. I even have an "expressions" table for which I'm not sure if feeling proud or ashamed, and in full disclosure also one sort of OTLT, that I'm thinking about removing now (but it's more complex then the usual ones and is about a good number of things). –  gab Apr 15 '13 at 14:35
    
@ColintHart About the proposal, personally I wouldn't see as cumbersome 5 or 10 of these tables if there was to be only one such structure for the whole database, if otherwise there were several it would easily get unwieldy. The distinction about the problems of nulls is a good point; to me the problem seemed to be the space usage, maybe it is not worth worrying? –  gab Apr 15 '13 at 14:36

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.