Foreign key to a generic code table

[Former Member]

Looking for a better idea:

When using a "generic code table" where I have a two-part key: (code_type, code_value) where code_type might be "gender" and code_values might be "male", "female" etc,

I have only figured out one way to have a foreign key to that table: in the main table I add two columns: gender_code_type and gender_code_value then populate every main row with the constant "gender" so I can construct the FK(code_type, code_value).

I tried FOREIGN KEY xx('gender', gender_code_value) using a string literal in the definition, but that doesn't work.

Is there another/better way to do this?

[Breck_Carter · Accepted solution]

[rant] The better way is to have separate code tables with single-column primary keys... 100, 200, 1000 tables, SQL Anywhere can deal with that far more easily than millions upon millions of two-column index entries in all those child table rows.

Another advantage to separate code tables is that when subtable 47 needs an extra column or a different data type or a different [impossible to predict], it is possible to implement without affecting the other 1999 subtables.

Don't let the pain of refactoring influence you: there is no need to immediately change all the existing code subtables... simply STOP implementing new subtables, use separate tables for new codes.

...and thus [/rant], before introducing The Twelve Step Program For Code Table Addiction 🙂

[VolkerBarth · Accepted solution]

SQL Anywhere can deal with that far more easily than millions upon millions of two-column index entries in all those child table rows.

I'd like to add that IMHO most human beings will also find a design with many different code tables easier to understand than one with a "generic code table" and a bunch of two column references...:)

[Former Member]

Rant gratefully accepted.

subtables, new attribites: yep. Had that problem.

"gender" is actually a great example: years back you could never have convinced me there would be any choices other then M and F. Much less sub-categories to some choices.

No re-factoring necessary: I dumped the generic code tables in 2003 after my mentoring sessions with you in Toronto. Periodically, "bright new ideas" come around again and I have to ask myself, "Am I doiing this because I have always done it this way, or because it is the BEST way". Thanks for the refresher!