Implementing small, fixed domains

[Kenny-Z]

Hi,

I have a schema design question... By 'small, fixed domains', I'm
referring to information suach as: Customer.Sex {Male, Female};
ClientPhone.Type {Home, Work, Cell, Pager, Fax}; ClientAddress.Type
{Billing, Shipping}; Customer.MaritalStatus {Single, Married,
Divorced, Widowed, Polygamist}...

On one hand, information like this can be modeled as an attribute of
an entity using integer values (for example), such as 1=Male,
2=Female... It is then up to the front-end application to translate
these into meaningful values. Advantages: less table joins=less
overhead, simpler to implement the schema.

On the other hand, they could be modeled as entities themselves and
implemented as separate tables which requires joins to 'translate'.
Advantages: domain values can be edited, added or deleted; perhaps
simpler to comprehend schema and design front-end app (no need to
refer to documentation to interpret the meaning of attribute values
such as '1, 2, 3', because this is exposed in the schema...).

Also, let's say that the types of domains we're talking about are
known from the start to be a fixed set that will not need to be edited
or amended by users, which, if they did would necessarily make the
case for a separate entity/table.

So what else should one consider when deciding how to actually
represent these types of values in the database schema?

thanks for any advice,

Ken

 

[Tim Ferguson]

I have a schema design question... By 'small, fixed domains', I'm
referring to information suach as: Customer.Sex {Male, Female};
ClientPhone.Type {Home, Work, Cell, Pager, Fax}; ClientAddress.Type
{Billing, Shipping}; Customer.MaritalStatus {Single, Married,
Divorced, Widowed, Polygamist}...

On one hand, ....

On the other hand,

....

and on the third hand you can implement a domain table: where the (short)
description field is the one and only column, and is also the PK and the
target for the FK in other tables. This has the advantage of not needing
joins for display, and total control.

All three are appropriate in different situations: you seem to have listed
the main advantages and disadvantages. One of my favourite tricks, as a
paediatrician, is filling in forms with "B" and "G" for boy and girl and
letting the data clerks at the other end figure it out. "M" and "F" for
mother and father are good too, or was it Male and Female parent...

All the best


Tim F

 

[TC]

(snip)

One of my favourite tricks, as a
paediatrician, is filling in forms with "B" and "G" for boy and girl and
letting the data clerks at the other end figure it out.

And of course, that hardy old perennial:

Sex: [ YES! ]

TC

 

[TC]

As Tim said, you've pretty much summed up the pro's and con's yourself,
already!

Personally I consider the *number* of domain values as well as whether they
are likely to change, or not. If there are more than "a few" - whatever "a
few" means - then you can bet they *will* change, wehatever the customer
says.

So for example, if someone needs to record sex, I'm happy to code-in M & F.
But if they need to record a "found out from" code:

Radio
TV
Newspaper
Friend
Internet
Walk-in

i'd be tempted to say, "there are already 6 values - it is inevitable that
there will be more, in due course, whatever the customer says"; so I would
put those in a table.

HTH,
TC

 

[Kenny-Z]

....


and on the third hand you can implement a domain table: where the (short)
description field is the one and only column, and is also the PK and the
target for the FK in other tables. This has the advantage of not needing
joins for display, and total control.

That sounds like a simple,compelling and clever third possibility that
I hadn't thought of...

thanks,

kz

 

[Kenny-Z]

As Tim said, you've pretty much summed up the pro's and con's yourself,
already!

Thanks, TC. I'm glad it appears I'm on the right track. I guess what
I'm looking for is to learn of the thought process others may use in
dealing with domains and making such judgements, such as your comments
below.

regards,

kz