Re: When to use check constraints and when to use small tables?



If you think the # of values is going to change, or the values are
going to alter, and you don't want to have to change the app or the
check constraints: put them in a table.

Well, what I was asking was when it is appropriate to
change the app by modifying check constraints on
a table - is the rule about no DDL in an application
an *_ABSOLUTE_* one, and if not, what are the
circumstances under which it should be allowed?


If the number of values is more than 3: always put them in a table.
You might forget a state or miscount them.

Ah, I see. This is a perspective I had forgotten about
when framing my original post - I forgot to think about
those application programmers for example who will
have to write reports on the data in the app - it's true
enough that anything beyond two or three is probably
too much - I mean gender is OK but what about title?

The part of my post which hasn't been addressed is
what sort of indexes to put on the Person table - i.e.
a bitmap index if it's only two values - what about higher
numbers - what's the rule of thumb for the cardinality
of bitmap indexes?

Also, the construction of these "minor" tables - should
they be indexed, and if so, what sort of index should be
used - and if there are differences, why?


Paul...

.

Relevant Pages

  • Re: Relationships. Does anyone use them?
    ... more overhead in creating an app and unnecessary...or do you believe ... One of the basic precepts of relational database design is to have the ... generally called constraints - I believe that is the term used in the ... What do I care if I didn't go in into the relationships window to set the relationship....for the most part its always done for me. ...
    (comp.databases.ms-access)
  • Re: Relationships. Does anyone use them?
    ... more overhead in creating an app and unnecessary...or do you believe ... One of the basic precepts of relational database design is to have the ... generally called constraints - I believe that is the term used in the ... One important constraint is what in Oracle is called the foreign key ...
    (comp.databases.ms-access)
  • Re: Performance considerations
    ... anything but their app etc...but even if that's the case, ... On the other hand, client side validation is important too, if for no other ... If you go with only backend Validation, ... a combination of validation with all of the necessary constraints on ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Multiplicity, Change and MV
    ... the db engine is completely responsible for data-integrity (ie ... constraints between keys of appropriate tables which the db engine or ... From a purist point of view, it should be the app. ... Data types are not available until created just like types ...
    (comp.databases.theory)
  • Re: Multiplicity, Change and MV
    ... code required to enforce constraints must exist in each and every ... From a purist point of view, it should be the app. ... those common app-constraints should not be embedded ... Data types are not available until created just like types ...
    (comp.databases.theory)