The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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.0k334673
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
26.6k433604877
accept rate: 21%

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.0k334673
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:

×22

question asked: 23 Jan '16, 12:45

question was seen: 308 times

last updated: 25 Jan '16, 02:44