Type tables are a common feature of many systems. You may want to create a standard layout for your type tables. I prefer something like:
model_id int PK NOT NULL,
description char UK NOT NULL,
active indicator (boolean, end date, other appropriate type)
You could use the abbreviation or description as the PK, but then you need to do cascading updates when the value changes. If you want to add internationalization in the database the description, abbreviation, and sort order can be moved out into a child table with a language field. Otherwise, you can use your languages internationalization support.
The description field should be a natural key for the type table. You may also want to enforce uniqueness on the abbreviation, and in some cases they are an alternate natural key. I usually sort with the sort_order first (nulls last) to enable bubbling common or default values to the head of the list.
Tables containing types would have a foreign key reference to the appropriate types tables. Some types tables will be unique to a particular parent table, but other types are may be shared by two or more tables. If you see table with similar contents but different names take a second look. You may have two names for the same type, a subtype, or just similar types. Model and build accordingly.
Active indicator can be checked by a trigger, but is usually validated by code.
Some types may have associated data with can be added to their types table as additional fields.
Alternatives (none of which I recommend):
- Check contraints on the type field,
- Enum fields,
- A common types table with triggers to validate, or
- Ignoring types altogether.
With proper maintenance of the types tables, you can develop code for new types, and release it to production. The code won't be able to use the new type until the type is in the type table, and if selections (drop-down, etc) are built from the database, interactive code won't even try to use them.
You may want to consider whether to cache the type descriptive data and just retrieve ids, or do the joins to retrieve the descriptions.