Many codes, how many tables ?

Lots of codes
This must be a fairy generic problem, so there must
be solutions for this 'problem'.
The problem :
Many different code tables.
For example :
Gendercode: M,F(Male, Female)
or Departmentcode
Codes like M, F (Male, Female) are likely to be solved in the
application. (But suppose the gender code is being extended
to M, F, U, O (Male, Female, Unknown, Other) as it is in the
RIM-model (Reference Information Model, a model used
in healthcare).
Some codes cary more information than other codes.
(Short description, long description, mnemonic, language etc.)
Some codes are almost similar but not the same :
The financial departmentcode and the organisational departmentcode.
What to do ?
1. Have one table for all codes.
2. Have several tables, one for each 'group of types' of code.
3. Have a table for each codetype.
4. Have a generic solution. (Four or six tables implementing a generic model
solution.)*
(In our legacy database, it was to expensive to have a table for each
type of code, so there we had a code table, which could contain all
codes for all sorts of applications. But this was not an ideal solution.)
Second question, What to do with language ?
Gendercode : M, W, N, A
(mannlich, weiblich, nicht, ander)
or
Gendercode : J, M, O, A. (Here the M stands for Female).
Did any of you run into this problem, what 'solution' did you choose ?
(And what are the arguments for / against the solutions ?)
Thanks for your time,
ben brugman
*) The generic solution can be used to store any type of
relational data in four or six tables. Mostly it is used
to store meta data, or data which can not be defined
exactly at the time of implementation.
The generic solution is very flexible databasewise, but
needs a lot of coding to make it usefull. And it needs
coding to implement the constraints. So it is less flexible
and less efficient for the application.One solution that we have used in our application is to define the concept
of "code types". This table tracks the various types of codes that the
application is expected to work with. There would be another table called
"Codes" which has the "Code Type" and a set of fields that are common to all
codes. You could track different languages in the same table also. Multiple
tables is also a solution that can work, but your application code needs to
refer to different tables. Since code tables are near static, you can
benefit by having clustered indexes on the "Code Type" and also pinning the
table if required.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"ben brugman" <ben@.niethier.nl> wrote in message
news:%23QMB0iv7DHA.2088@.TK2MSFTNGP10.phx.gbl...
> Lots of codes
> This must be a fairy generic problem, so there must
> be solutions for this 'problem'.
> The problem :
> Many different code tables.
> For example :
> Gendercode: M,F(Male, Female)
> or Departmentcode
> Codes like M, F (Male, Female) are likely to be solved in the
> application. (But suppose the gender code is being extended
> to M, F, U, O (Male, Female, Unknown, Other) as it is in the
> RIM-model (Reference Information Model, a model used
> in healthcare).
> Some codes cary more information than other codes.
> (Short description, long description, mnemonic, language etc.)
> Some codes are almost similar but not the same :
> The financial departmentcode and the organisational departmentcode.
> What to do ?
> 1. Have one table for all codes.
> 2. Have several tables, one for each 'group of types' of code.
> 3. Have a table for each codetype.
> 4. Have a generic solution. (Four or six tables implementing a generic
model
> solution.)*
> (In our legacy database, it was to expensive to have a table for each
> type of code, so there we had a code table, which could contain all
> codes for all sorts of applications. But this was not an ideal solution.)
> Second question, What to do with language ?
> Gendercode : M, W, N, A
> (mannlich, weiblich, nicht, ander)
> or
> Gendercode : J, M, O, A. (Here the M stands for Female).
> Did any of you run into this problem, what 'solution' did you choose ?
> (And what are the arguments for / against the solutions ?)
> Thanks for your time,
> ben brugman
> *) The generic solution can be used to store any type of
> relational data in four or six tables. Mostly it is used
> to store meta data, or data which can not be defined
> exactly at the time of implementation.
> The generic solution is very flexible databasewise, but
> needs a lot of coding to make it usefull. And it needs
> coding to implement the constraints. So it is less flexible
> and less efficient for the application.
>|||Unknown gender? :)
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||Yes the RIM-model does account for unknown gender.
(Even more strange is the 'Other' gender.)
In this world it is fairly common that the gender of
a registered person is not known.
And there are occurences whereby the gender of
a person is not clear. (For example XXY types,
persons with two X type chromosomes one Y type
chromosome. Most often catogorised as Female,
but some are catogorised as Male).
But that said, in our legacy system we did not
cater for anything else then male or female.
But in our 'new' database Unknown and Other
are 'possible' genders. But as far as I know there
are no registrations within this catogory (yet).
ben brugman
"James Goodman" <j a m e s@.norton-associates.co.u k> wrote in message
news:c085bb$ske$1@.sparta.btinternet.com...
> Unknown gender? :)
>
> --
> Cheers,
> James Goodman MCSE, MCDBA
> http://www.angelfire.com/sports/f1pictures
>