4

My concern is about what I currently call "dictionary tables", that are database tables containing a list of controlled vocabulary.

Let's use an example: Suppose you have a table User containing fields:

  • user_id : primary key
  • first_name
  • last_name
  • user_type_id : foreign key to the UserType table

and another table UserType with just two fields:

  • user_type_id : primary key
  • name : the name/value of a particular type of user.

For instance, the UserType table may contain (1, Administrator), (2, PowerUser), (3, Normal)...

My question is: what is the canonical term for a table like UserType, that only contains a list of (dictinct) words. I want to publish some code that help managing this kind of tables, but first I have to name them !

Thanks for your help.

Current state of thought: For now I feel Lookup Tables is a good term. It is also used with the same meaning in these posts:

The only problem is that lookup table is also sometimes used to name a junction table.

Community
  • 1
  • 1
Karl Forner
  • 4,175
  • 25
  • 32
  • 3
    I'd call it a lookup table. Not sure if that's the accepted term or not. – phillyd Sep 03 '12 at 15:36
  • +1 to Lookup Table. I'd call it that too. [My answer below](http://stackoverflow.com/a/12251008/1431750) was for the values in it which can then be called Lookup Values rather than Candidate Keys. – aneroid Sep 03 '12 at 18:45
  • Physically, that would be called a lookup table or metadata. What you're actually building with a series of these things would be a data dictionary. – Robbie Dee Sep 04 '12 at 09:13
  • @KarlForner please refer to the down vote/up vote FAQ. http://stackoverflow.com/privileges/vote-down – davidmontoyago Sep 04 '12 at 14:12

5 Answers5

1

I often see that list of words as the domain of a function (the set of input values allowed), so I call them Domain Tables. But it´s from a mathematical point of view.

EDIT

See:

davidmontoyago
  • 1,834
  • 14
  • 18
1

In my experience with SQL developers, the stronger their background in relational theory, the less likely they are to use terms like "lookup table", "validation table", or "dictionary table".

Instead, they just call them tables. Why?

For you, the important part seems to be tables that

  • contain only one text column, or
  • contain only one text column and an id number, or
  • contain only one text column and a short text code, and
  • the primary key is used as the target for foreign key references.

If you think about it for a while, the only thing that distinguishes these tables from others is the number of columns. Relational theory distinguish relations by the number of columns, and I don't feel the need for distinctions like that in SQL, either.

  • Every candidate key implements a controlled vocabulary in this sense--the key (and all the other applicable constraints) provide the mechanism that controls the "vocabulary".
  • Every candidate key can be used as the target for a foreign key reference, regardless of how many candidate keys a table has, regardless of how many columns a candidate key has, and regardless of whether any of the candidate keys are used as foreign key references today.
  • Many such tables only start their life as "lookup" tables. A year down the road, someone discovers the need to store more information. After you add one or two more columns, is it still a "lookup" table, or not?
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • It is not the only thing that distinguishes these tables from others. The number of rows is finite, and often quite small, and they are quite stable (few changes), making them perfect targets for caching for instance. In my application I have at least 10 such tables. – Karl Forner Sep 04 '12 at 09:18
  • The number of rows and stability might just be a coincidence. A table of country names would have around 400 rows, but one for city names might have more than 35,000 rows. – Mike Sherrill 'Cat Recall' Sep 04 '12 at 10:19
  • You make a good point. What to call these things depends largely on the target audience. Lookup table might mean a lot to a developer but be meaningless for a user (ditto other terms like domain). – Robbie Dee Sep 04 '12 at 10:38
  • @catcall: you are right, my intended use is to control the vocabulary, and to avoid hard-coded strings in code. – Karl Forner Sep 04 '12 at 11:34
  • @dee: the target audien is developers, I want to name a perl package. – Karl Forner Sep 04 '12 at 11:35
0

As a C coder I'd say that this table looks really like an enum (or enumeration). It exhaustively defines acceptable values and links an automatically given integer to a name (and vice-versa).

And as a SO user I'd say this question really looks a little too open as I don't think there is one unique canonical name...

Denys Séguret
  • 372,613
  • 87
  • 782
  • 758
  • Thanks for the answer. I'd just like to know the terms people use, so that I can lookup documentation and publish under an approprite name. And call a cat a cat, or a pussycat (I do not know if the translation from French works). – Karl Forner Sep 03 '12 at 15:57
  • As a French coder, I see what you mean ^^ But seriously I think `enum` (which I really use) precisely describes both what it is and how it is used. – Denys Séguret Sep 03 '12 at 16:08
  • I agree enum seems quite good, but I'm waiting to see if there's a community accepted term. Lookup could work too, maybe even better. – Karl Forner Sep 03 '12 at 17:14
  • I must confess, I've never come across enums (in the programming sense) in any of the RDBMSs I've used but it would be a very welcome addition. – Robbie Dee Sep 04 '12 at 09:45
  • 1
    [MysQL](http://dev.mysql.com/doc/refman/5.0/en/enum.html) and [PostgreSQL](http://www.postgresql.org/docs/9.1/static/datatype-enum.html) have an enum data type. But that's just for columns and can't replace an "enum" table. – Denys Séguret Sep 04 '12 at 09:50
  • I use it a lot to map with my java enums but it's not so useful as you can't easily extends mysql enums and you can't reuse an enum in different tables or columns (at least for MySQL, don't know for PostreSQL). – Denys Séguret Sep 04 '12 at 10:46
0

What you're describing is commonly called a Data Dictionary

Robbie Dee
  • 1,939
  • 16
  • 43
  • 2
    Are you sure ? Doing a quick search gives me http://database-programmer.blogspot.de/2008/06/using-data-dictionary.html and http://www.webopedia.com/TERM/D/data_dictionary.html that both disagree... – Karl Forner Sep 03 '12 at 17:11
  • 1
    That's not what a data dictionary is. +1 @KarlForner – aneroid Sep 03 '12 at 18:40
  • They're both correct. When I was a DBA, the file manifest was indeed also called the data dictionary. Like a lot of things in IT, there are that many terms/acronyms where the meaning depends on the context e.g. http://en.wikipedia.org/wiki/ASP#Computing – Robbie Dee Sep 04 '12 at 08:39
  • @aneroid Which definition are you disagreeing with? – Robbie Dee Sep 04 '12 at 09:47
  • @ RobbieDee I am disagreeing with your definition. Agreeing with @KarlForner's statement. – aneroid Sep 04 '12 at 09:58
  • @aneroid What would be your definition of data dictionary? – Robbie Dee Sep 04 '12 at 10:25
0

You could go the complete opposite direction and call them by their technical name rather than their meaning and let ppl infer that -- You could call them candidate keys - which makes sense in a "pick the candidate of your choice" kinda way; and each candidate is unique* (or supposed to be).

Nomenclature issues tend to be fun when they're not total headaches :-)

aneroid
  • 12,983
  • 3
  • 36
  • 66
  • 1
    I don't really understand your proposition, even after reading about candidate keys ! – Karl Forner Sep 04 '12 at 09:05
  • By opposite I meant - searching for technical vs. meaningful names - the Candidate Keys are values in the Lookup Table. Those are the values you actually pick from, in the tables they are referenced. So the `UserType.user_type_id` are Foreign keys for `User.user_type_id`, or to put it in a _meaningful_ way, `UserType.user_type_id` **and** `UserType.name` are Candidates for values in `User` (in those specific columns, `User.user_type_id` here). You asked what the canonical name for the `UserType` **table** should be -- and I'm saying I agree with @phillyd 's proposed term **`Lookup Table`**. – aneroid Sep 04 '12 at 10:15
  • I thought candidate keys were columns in the Lookup table ? – Karl Forner Sep 04 '12 at 11:36
  • Yes, the key is an index/rule for the column but what you're conceptually doing is making a link to a value in that column. Further exploration of "column" vs. "column value in a specific row" is just semantics. "Keys" are always columns and the usage is based on the values of that column. But you're looking for "something to call it" not get into technical definitions to confuse your users, right? Lookup Table/Column/Value suffice for such explanations. Otherwise, just call it a Parent Key/Parent Key Table. – aneroid Sep 04 '12 at 11:59