Constant Table

A RDBMS table or tables that store definitions of constant values or known abbreviations. It is often not practical to store longer descriptions of various options, so abbreviations are used. However, a place is needed to put those abbreviations and their definitions. First a simple example:

Abbrev. Descript. ------------------- TEL Telephone EML Email LET Paper Letter

A fancier schema may look like:

Table: constantSet --------- setID setDescript // example: "contact methods", "US States"

Table: constants ---------- setRef// foreign key to constantSet abbrev descript notes

Ideally, the database DataDictionary would assign columns using abbreviations to the corresponding constant set so that referential integrity can be easily done on the abbreviations. It is possible to enforce proper constants with INSERT and UPDATE triggers also, but more effort.

Some suggest having a different constant table for each set, but I prefer a single table to keep the table-space simpler and factor schema patterns to a single spot in the spirit of OnceAndOnlyOnce. The only real drawback is if more specific information is associated with abbreviations, such as the population or capital of each state (in the US). In that case, it would perhaps be time to spin off a new entity. Or, maybe just have a few "parameter" columns, which are described in the constantSet table. Some find such hokey, but I have nothing against it as long as one is willing to make a fuller transition to an entity when the parameters grow too numerous.

I notice that most non-trivial systems have a fair amount of constant/abbreviation lists, but often there is no effort to formalize them to improve validation, reuse, and consistency.

-- top

One may be able to avoid having the "constantSet" table by putting the group key and description into the constant table itself.

Indeed. However, some developers get confused by self-joins, generating calls to the DB designer.

ConstantTable looks a lot like a thin table to me, in which case Top would be a hypocrite for saying that thin tables are too hard to grok or that they are bad and that wide tables are better. i.e. this constant table would be similar to an enumeration or it would be normalizing a database.

I never ever said that all thin tables were bad. Thus, as usual, I don't qualify for the insult you implied. Conversely, not all wide tables are bad. Like most things in life, it is about balance. And please, let's not spread the thin/fat table battle here also. --Top

I HaveThisPattern. It's a convenient way to place the set of allowed values under control of the user instead of the programmer. Adding a new value could be as simple as a web control panel. -- JonathanTang

In relational theory, a domain (roughly equiv to column type for SQL DBMS) defines [by either intension or extension] a time invariant set of values, while a relvar (roughly equiv to a table for SQL DBMS) defines [by extension] a time varying set of values.

In theory, then, such "constant" lists (actually sets, in most/all cases) should be defined as domains (i.e. as types in the DB) not as tables. Enforcing the domain constraint may also be faster than enforcing the FK-to-constant-table constraint).

But does the theory preclude tables from being used to implement "domains"?
No, though in that case they wouldn't be considered domains. TheThirdManifesto devotes an appendix to this, "Appendix B -- A Design Dilemma" in the 3rd edition. To quote: "Overall, we believe the most appropriate design [i.e., which approach to use] will emerge if careful consideration is given to the distinction between (a) declarative sentences in natural language on the one hand, and (b) the vocabulary used in the construction of such sentences on the other. [...] [I]t is unencapsulated tuples in relations that stand for those sentences, and it is encapsulated domain values in attributes in those tuples that stand for particular elements -- typically nouns -- in that vocabulary. To say it slightly differently [...] domains, or types, give us values that represent things we might wish to make statements about; relations give us ways of making those statements." (page 380). Hope this helps... -- DaveVoorhis

However:

SQL DBMSs typically have poor support for user defined types (e.g. no PossRep? or other structured type support)
SQL DBMSs typically have poor introspection support for user defined types e.g. no way to retrieve the list of valid values for an extensionally defined set (i.e. an enumeration) to populate a drop-down
often the boundary between "time invariant" and "time varying" is rather nebulous; after all everything changes at some point
as JT notes above, it is convenient to allow users to change these things without requiring an IT project, even for "very slowly varying" sets. Here the lack of introspection hurts again, and, of course, SQL DBMSs (and their DBAs) are not typically set up to allow DDL to be executed from applications (even where the DBMS security features allow restrictions such as "user U can ALTER domain X but not CREATE/ALTER/DROP anything else" to be set up).
Types may be orthogonal to relational and conversion to and from may be tricky. See DoesRelationalRequireTypes.
Access or usage of constant types within domain languages using RDBMS is not standardized nearly as much as SQL (which is only half-standardized).

So often we are better off using tables instead of types for these "constant sets".

They should perhaps be interchangeable. I see no need to invent a new "list type" when we have a table engine right under our feet. Some linguistical or IDE shortcuts to maintain them may be helpful though.

But consistency such as (lisp(is(lisp(and(can(create more)lisp)))) isn't always a good thing i.e. Everything Is an Object, Everything is a Table, Everything Is a Function. Everything is a message. Eek. --DevilsAdvocate

"Everything is a new type because reuse of concepts bothers me" is also a form of EverythingIsa-ism. I am not sure we can escape your complaint without making the very same mistake again. Lisp did get something wrong, but I cannot quite put my finger on it yet.

The everything is just a table solution is worse than some things being tables, some things being types (constraints), some things being in the database, some things being code, etc

Why use two different collection systems when we can reuse an existing one? Plus, fairly often items in constant tables evolve to become their own table. For example, we may have city names in the constant table, but later want to store specific stuff about each city. It's easier to migrate table-to-table than list-thingy-to-table.

Why not use a multi dimensional array[0][0] for every problem in the world, including storing a single integer or string? --DevilsAdvocate

One could argue that MultiParadigmDatabase or DynamicRelational is more or less this kind of thing in map form, which I am all for. However, my favorite HobbyHorse structure is probably not yours. This is the point where I'll claim its a personal preference, but you claim there is some involved math that "proves" types better for some things, and it will trigger yet another BookStop/empirical-evidence fight. But do "types" and "tables" have to be distinct? What if half the type system can be table-based even if not all of it can?

One thing to consider is that tables seem to offer better cross-tool sharing capability. See CrossToolTypeAndObjectSharing. --top

CategoryInfoPackaging, CategoryDatabase