Re: Table design question

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.

-- 
Mike Sherrill
Information Management Systems