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?
asked 23 Jan '16, 12:45
[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 :)
answered 24 Jan '16, 08:42
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!
answered 24 Jan '16, 10:48