Re: Table design question

From: D Guntermann <guntermann_at_hotmail.com>
Date: Tue, 10 Feb 2004 02:46:06 GMT
Message-ID: <HsuL0s.AI3_at_news.boeing.com>

[snip]

> > >
> > > 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.
> >
> > This is quite a presumption. While it could be one reason, it's not the
> > only possible reason for defining a "complex" enumerated domain.
> >
> > Quite the contrary, if a system is "modeled" to represent something in
the
> > real world upon which we want to make decisions or have the system
execute
> > upon, then modeled domains "by constraint" at the logical level are
entirely
> > valid. I further submit that having the database management system
enforce
> > domains and therefore its consistency is not only desireable, but
necessary.
> >
> > Tomorrow, a new user may be enrolled, another
> > > may leave etc.
> >
> > Then you change the implementation (the implemented constraint) to
reflect a
> > change in the logical model as dictated by the user or business. It's a
> > fact of life that systems in the real world evolve, but it doesn't
change
> > the fact that logic should to be deterministic to reflect the real world
and
> > provide some modicum of behavior that is both expected and that conforms
to
> > the model.
> >
> > 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.
> >
> > I think everyone agrees that there is a penalty involved with enforcing
> > certain constraints to ensure quality of the system, but that is not
what is
> > at issue. People argued, and still argue, that association by value as
a
> > performance liability for the same reasons - to justify their rational
for
> > not accepting something.
> >
> > Much
> > > more typical would be "between 5 and 10 alhphanumeric characters" or
> > > "an integer between 1000 and 9999".
> > >
> > I don't understand the criteria upon which you decide that one degree of
> > specificity in defining a domain of representations in the form of
strings
> > or numbers is necessarily better than another.
> >
> > For example, if I devised a logical model for "processes" where I defned
> > states of a process at a logical level as only being started and
completed,
> > I'd want to ensure that 1) the system only allowed for two state values
for
> > any given process; 2) being forced to use SQL enforcement at a lower
> > representation level, I chose two representations and ensured that each
> > representation identified the correct state; and 3) that the DBMS could
> > enforce state transitions if it was so desired.
> >
> > I could choose any two strings or set of two numbers to represent those
> > states, but declaring and defining column variables as VARCHAR(7) or
> > INTEGER(1) will do nothing to enforce the integrity of my model from a
data
> > management perspective. If I chose to represent my domain as the
strings,
> > 'STARTED' and 'COMPLET', or alternatively as 1 and 2, then the
VARCHAR(7)
> > domain definition will not enforce the two state domain in that it
allows
> > for 562,949,953,421,302 states other than the two I want - assuming an
> > alphabet that allows for 2^7 symbols, which is equivalent to 28
characters
> > and the disallowance of null. In the case of INTEGER(1), the DBMS
would
> > allow for 8 additional states that are not part of my logical domain.
> >
> > Since we are dealing in large part with string and number
representations,
> > we might consider a defined language (sequences of symbols composed of
> > alphabets upon which rules of syntax and grammer are applied) a proxy
for a
> > defined domain.
> >
> > If you want to argue the point by saying that you have to generalize
data
> > and domain definition to allow for evolution and incremental change to
what
> > one today defines as the domain, then who is to say the VARCHAR(7) or
> > NUMBER(1), or alternatively, alphanumeric consisting of between 5 and 10
> > characters, is sufficient? Why not just call everying attributeN and
> > defined everying as alphanumeric 100 wide character strings?
> >
> > Even more importantly, what assurances can the DBMS give me that my
universe
> > or model of a shared real world is properly being represented at the
current
> > time?
> >
> > > I agree that SQL database don't support domains very well, but the
> > > example being discussed here is NOT a domain.
> >
> > Yes it is. It's just a rather inconvenient domain.
> >
> > Best regards,
> >
> > Dan

>
> What can I say?  I really thought this was quite a simple concept, and
> yet you have completely failed to comprehend me.

Well, I don't think I did. But I can see where the disconnect is. You presume that the enumerated domain under discussion represents actual users at time *now* as part of a set of propositions that meet some predicate. I did not see where that was clearly stated in Mr. Sherrill's example and one of your statements allowed for the domain being discussed to be an enumerated domain of "allowable" values, which, given the contradiction, probably confused me.

Another part of the disconnect is that we are discussing this at two different levels - your point, if I understand you correctly, is that you want to arbitrarily define some pool of numbers that works as an identity function, generating user ids for users, now and forever, which I admit is entirely valid.

My argument was from a more generalized point of view and did not work under the assumption that the domain under discussion reflected actual current users, in contrast with the pool of allowable user_id's; but rather reflected the viewpoint that the domain was an enumerated domain, representative of any domain that might be finite under some model of logic. Examples of this might be social security numbers or phone numbers.

Are you seriously

> saying that if I run a business with 5 employees whom I assign the
> user_id's 5, 7, 42, 101 and 123 then any tables that have a user_id
> column are in the DOMAIN "user_id" that is restricted to the values
> (5, 7, 42, 101 and 123).

I won't belabor the point.

I think Bob is right in that we are talking past one another, but nonetheless, is there some criteria that states that a domain constraint is ok in one instance but ridiculous in this particular case? Where is that line? I suppose its where the user draws it.

So if tomorrow I hire a 6th employee I have > to redfine the "user_id" DOMAIN to include another value? >
See leading comments....

> Sorry, but you are talking nonsense.

I'm sorry you feel that way. Hopefully we can put it behind us. I still stand behind my comments though, given my own set of assumptions.

I will add a new user to the

> USERS table with a user_id taken from the DOMAIN of user_id's, which
> may be (for my system) a number between 1 and 999, or a character
> string of up to 10 characters.  I am NOT restricted to the list id
> user_id's that have currently been allocated to existing employees.
>

Whoever said you were restricted in such a way? I did not intend to give the impression I was supporting that particular viewpoint at all.

> My point is not that an enumerated domain is impossible, but that if
> one is defined it should be complete.  It does not consist solely of
> the subset of values that I happen to have used to date.

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 Received on Tue Feb 10 2004 - 03:46:06 CET