Re: Table design question

"D Guntermann" <guntermann_at_hotmail.com> wrote in message news:<HsuL0s.AI3_at_news.boeing.com>...
> [snip]
> I agree with your sentiments to a point, though I still think there are
> exceptions, generally speaking. I'll concede this particular case. Sorry
> for any misunderstanding.
>
> Regards,
>
> Dan

Sorry the tone of my post last night was rather strong - it was late ;o)

The issue I am dealing with started with this from Karel Miklav:

>CREATE TABLE user (
> id INT NOT NULL ) <---------------------
> |
>CREATE TABLE user_history ( |
> user_id INT NOT NULL, oo----------------|
> valid_from DATETIME NOT NULL, |
> name CHAR(100) NOT NULL, |
> ... ) |
> |
>CREATE TABLE log ( |
> id INT NOT NULL, |
> tstamp DATETIME NOT NULL, |
> user_id INT NOT NULL, oo----------------
> description CHAR(100) NOT NULL,
> ... )
>
>There are other related tables like log here, where I'm really not
>interested in historical data but just the identity of the user, and
>those where exact user properties in point of time matter.
>
>The one-field user table smells like a design problem. Merging user
and
>user_history tables introduces ugly problems with relational
integrity.
>Are these one-field tables normal or how am I supposed to tackle
this?
>Please share your thoughts and links.

To which Mike Sherill responded:
>Smells like a domain.
>
>At the conceptual level, every domain is just a column (or several
>columns) of values. Think of it as a domain of user ID numbers.
(And
>call it "user_id" if you can.)

Now, I am saying that *in this case under discussion* the USER table is (intended to be) a perfectly valid 1-column table of users, users who actually exist. It is not a DOMAIN containing all the possible user_id values that could be used, it is the subset that are currently in use. Of course the column USER.ID is IN a domain (INT), and could have been in a more specific domain "USER_ID" with some more constrained set of possible values such as 4-digit numbers, prime numbers or whatever. Received on Tue Feb 10 2004 - 11:36:38 CET