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

Bill%20Aumen's gravatar image

Bill Aumen
2.1k354775
accept rate: 16%


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

permanent link

answered 24 Jan '16, 08:42

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

2

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

(25 Jan '16, 02:44) Volker Barth

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!

permanent link

answered 24 Jan '16, 10:48

Bill%20Aumen's gravatar image

Bill Aumen
2.1k354775
accept rate: 16%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×31

question asked: 23 Jan '16, 12:45

question was seen: 1,838 times

last updated: 25 Jan '16, 02:44