[AccessD] (no subject)

Hello Ed,

I have seen this in an Oracle database on one accasion.  Of course, it is
not a relational database when you store lookup tables like this, or at
least, it is no possible to enforce good referential integrity.  But... in
that company, once we got used to the little function that we used to
retrieve values from the lookup, it worked a treat.  And to make matters
even better, when we wanted a new lookup table added, we could do it
ourselves without having to go to a DBA.  In that company, they have about
one hundred look tables, so it was neater than if they were all stored in
seperate tables.

In summary, I would not do it, but I did see it working effectively.

Mark



On 24/04/07, Tesiny, Ed <EdTesiny at oasas.state.ny.us> wrote:
>
> Hi All,
> I'm not familiar enough with SQL Server but I have a question regarding
> what I call Code Tables.  I use them a lot when I develop an application
> in Access, e.g., I'll have a table for counties i.e., county code and
> county name or Providers, Provider code and Provider Name.  I have them
> as separate tables.  I'm trying to make sense out of the tables and
> relationships "my" programmer created.  He has one code table period!
> Below is a look as to how it is setup.
>
> CodeType     CodeId     CodeName                OrderOnForm
> Ethnicity      1        Puerto Rican                 1
> Ethnicity      2        Mexican                      2
> Ethnicity      3        Cuban                        3
> Ethnicity      4        Other Hispanic               4
> Ethnicity      5        Hispanic, Not Specified/Known5
> Ethnicity      7        Not of Hispanic Origin       6
> Ethnicity      9        Don't Know/No Answer         7
> Gender         1        Male                        10
> Gender         2        Female                      20
> Health         1        Poor                         5
> Health         2        Fair                        10
> Health         3        Good                        15
> Health         4        Very Good                   20
> Health         5        Excellent                   25
> Health         9        Don't Know/No Answer        80
>
>
> This is just a little bit of the table but I think you can see his
> "logic" here.  Is this a common convention that developers use?  Hate to
> see what else I'm going to find as I try to wade through this.
> TIA
> Ed
> Edward P. Tesiny
> Assistant Director for Evaluation
> Bureau of Evaluation and Practice Improvement
> New York State OASAS
> 1450 Western Ave.
> Albany, New York  12203-3526
> Phone:  (518) 485-7189
> Fax:  (518) 485-5769
> Email: EdTesiny at oasas.state.ny.us
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>