Obsługa enumów w dużym systemie z bazą danych

Mam taki problem który się ciągnie latami i jeszcze nie spotkałem zadowalającego rozwiązania.
Często w dużych systemach jest tak że mamy jakiś enum i chcemy go trzymać w bazie. Powiedzmy że np jest klasa Kot i enumem jest rasa.
Pod uwagę trzeba wziąć że system jest duży, do bazy podpiętych jest kilka aplikacji klienckich i serwerowych a możliwe jest dalsze rozwijanie.
W jaki sposób to rozwiązać? Widzę 3 podstawowe możliwości.

1. Robimy w kodzie enum i do bazy zapisujemy jego intową wartość.
   Zalety:
   -Proste

• Ładna obsługa z poziomu kodu
  Wady:
• Baza jest bezużyteczna w oderwaniu od kodu.

2. Robimy tabelkę zawierającą definicję wszystkich enumów w systemie, przypisujemy im wartości liczbowe. (Ewentualnie dwie tabelki w relacji 1 do n, jedna dla definicji enuma a druga dla konkretnych jego wartości). Przy tabelce używającej enuma wpisujemy wartości liczbowe ale bez tworzenia relacji między tabelami.
   Zalety:

• Baza jest samowystarczalna tzn można odczytać enumy bez kodu aplikacji
  Wady:
• Nie wiem jak sprytnie zrobić obsługę tego z poziomu kodu żeby było to faktycznie elastyczne tzn bez posługiwania się stringami (etykiety enuma).

3. Robimy dla każdego enuma tabelkę i łączymy z daną - zainteresowaną tabelką relacją n do n.
   Zalety:

• Baza samowystarczalna
  Wady:
• Też niezbyt piękna obsługa z poziomu kodu
• Bardzo dużo małych tabelek w bazie.

Żadne rozwiązanie nie jest ok. Jak wy to robicie? Bo jestem pewien że jest to częsty problem.

W systemach przy których pracowałem zazwyczaj było rozwiązanie 3. Raz w życiu spotkałem się z rozwiązaniem 2 i to nie było wygodne.
Podejście 1 w ogóle się nie nadaje do przechowywania ras kotów i tym podobnych rzeczy, które po pierwsze muszą mieć jakąś nazwę, a po drugie musi być możliwość łatwego dodawania/edycji takich danych.

Bardzo dużo małych tabelek w bazie.

Niekoniecznie dużo i niekoniecznie małych. A nawet jeśli, niekoniecznie jest to coś złego ;-)
Jeśli chcesz w bazie przechowywać jakieś inne dane nt. rasy oprócz nazwy (np. opis) to oczywiście pójdzie do tej tabelki.

Pierwsze podejście nie jest złe gdy wartość enuma nie zmienia się zbyt często.

Jak to się robi w naprawdę dużych systemach:

• w kodzie zapisujesz deklarujesz i używasz swoich enum
• w bazie zapisujesz w tabelach biznesowych kody enum oraz dla raportów tworzysz tabelę per enum lub globalną z opisami enum

Pseudo-kod:

create table zamowienie(
zamowienie_id int NOT NULL, 
data_zlozenia date NOT NULL,
data_realizacji date NULL,
status_zamowienia int NOT NULL
);

create table status_zamowienia(
kod int,
nazwa varchar(30)
);

insert into status_zamowienia(kod, nazwa)
values(1,'Otwarte');
insert into status_zamowienia(kod, nazwa)
values(2,'Zrealizowane');

insert into zamowienie(data_zlozenia, status_zamowienia)
values('2012-02-12',1);

Zalety:

• kod aplikacji używa tylko tych enum, które zna i nie musi zaglądać do dodatkowej tabeli żeby na nich operować (nadmiarowe relacje są mordercą wydajności)
• baza danych może implementować zestawienia SQL-only

Wady:

• baza sobie a aplikacja sobie, dwa miejsca deklaracji enum (ale problem jest tylko jeśli aktualizujesz pola enum także w SP/triggerach)

(na podstawie pracy w systemach klasy mainframe/midrange)

Uzupełnienie:
Pole status_zamowienia.kod NIE jest typu auto-incremental, jest kodem, którego używamy w aplikacji. Jednocześnie jest wartością przechowywaną bezpośrednio w polu zamowienie.status_zamowienia.

vpiotr napisał(a)

nadmiarowe relacje są mordercą wydajności

Już jakieś parę lat temu ;) wymyślili coś takiego jak OLAP i hurtownie danych. Polega to na tym, że dane się najpierw replikuje i przekształca do przyjaznego formatu, denormalizuje, a dopiero później poddaje analizie i generuje raporty. Od tamtego czasu problem nadmiarowych relacji w bazie stał się mniej palący i można sobie pozwolić na zapytanie z 10 joinami, ponieważ w systemie operujemy głównie na "malutkim" zakresie danych w stosunku do całości.