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

"Kenny-Z" <reply (AT) group (DOT) please> wrote

I really don't see the difference. Logically:

1 = 1*2^0, 2 = 1*2^1+0*2^0
'Male' = 'M'*128^3 + 'a'*128^2+'l'*128^1+'e'*128^0
where 128 is ascii range.

Physically, any reasonable database implementation should be able to
compress a column storing {'Male','Female'} to something close to 1 bit/per
row.