Re: Table design question

Mike Sherrill <MSherrillnonono_at_compuserve.com> wrote in message news:<pp7c20dospovtg3vv925vhtrtikgdvm190_at_4ax.com>...
> On Fri, 30 Jan 2004 12:46:56 -0500, "Bob Badour" <bbadour_at_golden.net>
> wrote:
>
> >> Try thinking about it this way . . .
> >>
> >> At the conceptual level, a domain is just a data type, and a type is,
> >> among other things, a set of all possible values. One way to handle a
> >> set of values is to store them in a table.
> >
> >It's the "among other things" that kills you. A variable is not a time
> >invariant set of values and their associated operations.
>
> It doesn't kill *me*. SQL databases don't support domains in the
> relational sense of "domains". That's why it's often helpful to try
> thinking in other ways.
>
> In designing a SQL database based on a logical model, you have to map
> types, relvars, and assignments (among other things) from the logical
> model to features supported by the target platform.
>
> This
>
> TYPE USER_ID POSSREP ( INTEGER )
> CONSTRAINT THE_USER_ID IN {1, 112, 314, ... 9997};
>
> (where the enumeration of values is unavoidable) *might* map to
> something like this for a primitive data type
>
> CREATE DOMAIN User_ID AS INTEGER;
> CONSTRAINT user_id_values
> CHECK (VALUE IN (1, 112, 314, ... 9997));
>
> But that probably won't work. (And it's not even worth trying for any
> type more complex than a primitive data type.) Most SQL systems won't
> let you have more than a few hundred values in that CHECK constraint.
>
> Instead, you might map the relational TYPE...CONSTRAINT to a SQL
> CREATE DOMAIN statement, a CREATE TABLE statement, one INSERT
> statement for each value, one or more CHECK constraints, a bucketful
> of REVOKE statements, a FOREIGN KEY constraint from each table in
> which User_ID is (cough) used, and some administrative procedures to
> make sure the table is always used. *If* the designer is careful.
>
> Do you know a better way to map that TYPE...CONSTRAINT to a SQL dbms
> like, say, Mimer SQL? (I mention Mimer because you can download it
> from www.mimer.com.) Let's say you have six thousand non-consecutive
> integers to enumerate.

The only reason you are trying to enumerate "six thousand non-consecutive integers" is because you are under the mistaken impression that the set of users who actually use a system is a domain, when it isn't. Tomorrow, a new user may be enrolled, another may leave etc. By contrast, the domain "user_id" is the (fixed for all time) set of all possible values from which users may be given a user_id. Of course, it is conceivable that an organisation might decide in advance that all user_id values must be taken from the enumerated set {1, 112, 314, ... 9997}, but if so then of course they pay the penalty in the complexity of enforcing the constraint. Much more typical would be "between 5 and 10 alhphanumeric characters" or "an integer between 1000 and 9999".

I agree that SQL database don't support domains very well, but the example being discussed here is NOT a domain. Received on Mon Feb 09 2004 - 12:30:03 CET