CODEWITHSUNDEEP
sqlterminologyrdbmsdatabase
Karl Forner
Karl Forner

Reputation: 4414

Finding the right terminology for a dictionary table

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:

and another table UserType with just two fields:

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.

Upvotes: 4

Views: 1583

Answers (5)

davidmontoyago
davidmontoyago

Reputation: 1833

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:

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95582

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?

Upvotes: 1

aneroid
aneroid

Reputation: 15962

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 :-)

Upvotes: 0

Robbie Dee
Robbie Dee

Reputation: 1977

What you're describing is commonly called a Data Dictionary

Upvotes: 0

Denys Séguret
Denys Séguret

Reputation: 382160

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...

Upvotes: 0

Related Questions