Re: Why does 1:3 relationsihp require another table?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Steve Kass (skass_at_drew.edu)
Date: 05/15/04 

Date: Sat, 15 May 2004 01:52:58 -0400

Louis,

  I agree that for quite a few kinds of small variations on the original
real-world model, the two-table design can more easily be changed into a
correct design for the new real-world model. That's a separate and
no-less important issue from the issue of whether the one-table model is
1NF or not.

  The reason you gave for calling the one-table model non-1NF, that not
all rows have the same number of columns, says to me that you want the
use of NULL to represent "doesn't exist" to always be a violation of
1NF. That's worth considering, but I think I can weasel out from under
it. ;) In order to do that, I'll assume you believe in NULLs, and that
there can be a relation with NULLs in its tuples (hence 1NF models with
NULLs, since every relation is 1NF).

  Suppose Louis's original password was ABC, and he changed it once, to
DEF. Lewis started out with 123 as his password, and has changed it to
456, then again to 789, and again to 012. I think you're claiming that
the three-password model is not 1NF because it then takes the form of
this non-relation:
NR={(Louis, DEF,ABC),(Lewis,012,789,456,123)}. NR is definitely not a
relation, since it's not a subset of any cartesian product of domains
(I'm using a notation that can only represent this kind of non-relation
if the"missing" attributes are all at the end, but I don't think that
obscures anything).

  But R={(Louis,
DEF,ABC,doesNotExist,doesNotExist),(Lewis,012,789,456,123)} is a
relation, and 1NF, since R is a subset of {strings} X {strings union
{doesNotExist}}X {strings union {doesNotExist}}X {strings union
{doesNotExist}}, assuming we are allowed to work in the mathematical
world of three-valued logic. doesNotExist is what some people call NULL
when they extend non-NULL relational algebra and step into the muddy
waters of 3VL. The fact that I didn't call it NULL is certainly
irrelevant. Since you believe in NULL, you believe that R={(Louis,
DEF,ABC,doesNotExist,doesNotExist),(Lewis,012,789,456,123)} is 1NF, so I
can only conclude that you do not believe Louis and Lewis are correctly
represented by R, and that NR is what my model is in the scenario I
described.

  Here's a good argument for not letting me represent Louis and Lewis by
S. If I did, Louis would not be able to ask for some good password
suggestions by inquiring

select p from someGoodPasswordSuggestions
where p not in (
  select password from S where Name=Louis
  union all
  select oldPass1 from S where Name=Louis
  union all
  select oldPass2 from S where Name=Louis
  union all
  select oldPass3 from S where Name=Louis
)

I agree that this query returns no value when applied to R, regardless
of the cardinality of someGoodPasswordSuggestions. I also know this is
an ugly query, but that doesn't prove anything.

It's not a bad attempt to show that S can't be what we have, but it's
based on the false assumption that SQL implements relational algebra,
which it doesn't. SQL is an attempt to implement relational algebra
with NULLs that mean UNKNOWN. But what NULL, or any element of any
domain, for that matter, means, is irrelevant to the determination of
whether something is 1NF or not, and this argument boils down to the
argument "You can't use NULL for anything but UNKNOWN". Of course you
can, but if you do, SQL does become a little less intuitive.

In my book, R={(Louis, DEF,ABC),(Lewis,012,789,456,123)} does represents
the state of a real-world model. Not the one we have, but one where
Louis is only prohibited from using his most recent password, and Lewis
is prohibited from using his three most recent. The model R, which is
not 1NF, represents, happens indeed to be a good example of a variation
on the original model that a simple variation on the one-table model
can't represent. I don't claim to be able to design a 1NF model for
that scenario, though, and the fact that the one-table model can't be
changed only slightly to represent something it isn't claiming to
represent is a separate issue from whether it's 1NF. ("That can't be an
animal, because it doesn't look anything like a peacock"). In my model,
each user is prohibited from using his or her last three passwords, and
Louis's NULLs means that there is a place in the model for a second and
a third most recent password, but no string currently has that role.
Fourth most recent passwords and beyond are not modeled, and there is no
place for them.

Well, I doubt I've made a convert of anyone, but I hope we all agree
that the questions are interesting and worth thinking hard about.

SK

 

Louis Davidson wrote:

>Actually address1 and address2 are very good examples of a 1nf violation
>that we all just kind of live with. ( I personally just go with a column
>called streetAddress and let it include carriage returns and line feeds most
>of the time.
>
>The problem with the solution of the old passwords twofold. 1nf requires
>that every row have exactly the same number of values. This is a problem
>when the user is created, since they only have 0 old passwords, so the row
>looks like:
>
>UserId ... Password OldPassword1 OldPassword2 OldPassword3
>1 fred null null
>null
>
>Ignoring that symantically what you are saying here is that the old
>passwords are unknown (they are actually known to exist) what do you do then
>the user changes their password to fred1
>
>UserId ... Password OldPassword1 OldPassword2 OldPassword3
>1 fred1 fred null
>null
>
>you put the value in OldPassword1, easy enough, but when they change to
>fred2, where do you put the value? You shift right, using tacky procedural
>code:
>
>Set oldPassword3 = oldPassword2, oldPassword2 = oldPassword1, etc
>
>Okay, but the real problem comes in when your users realize, that to keep
>their old password, all they have to do is change their password 4 times,
>and they are back to where they started (windows lets you, I have used this
>trick on a server when the password was being forced not reuse passwords for
>3 times. So you have the last time they changed the password (it is one of
>the columns represented by ...) but what if you want to get smart and not
>allow a password reuse for 2 months, or a year, or ever? Or what if you
>want to do some sort of matching algorithm to determine if the user is just
>reusing a root value (which users frequently do) or to look for some sort of
>pattern.
>
>By building the table like:
>
>UserPasswordHistory
>
>UserId DateChanged Password
>1 20040101 fred
>1 20040201 fred1
>1 20040301 fred2
>
>You give yourself the power to do this very easily, and with way more
>flexibility. Most of the time, more tables does not equal more complexity,
>usually less (from the standpoint of the database developer, which frankly
>is all we care about here :) because now the meatier bits of code are done
>in simple set based operations. A trigger can be written to maintain the
>number of password revisions allowed, and even a settings table could be
>created to allow this setting to be changed by changing a single value. The
>trigger could delete all rows that were greater than the number set in the
>settings, and/or to disallow reuse of a password for N months.
>
>The goal of doing proper design is flexibility to store and use the data
>using relational techniques, not using hokey procedural code, since the SQL
>language was created to deal with sets of data, not scalar values. I am not
>going to lie to you, it can certainly be done the way you have suggested,
>and you will not likely have any real problems with it, unless the user
>changes their mind (which almost never happens.) It will be more trouble
>to code (in comparison to using SQL as it is intended,) and you will lose
>information that may become useful to you ("When did I change my password
>last month?")
>
>
>
>
>
>
>

Relevant Pages

  • Re: Why does 1:3 relationsihp require another table?
    ... Pro SQL Server 2000 Database Design ... > there can be a relation with NULLs in its tuples (hence 1NF models with ... > can only conclude that you do not believe Louis and Lewis are correctly ...
    (microsoft.public.sqlserver.programming)
  • Re: On specialization constraints time of application
    ... Time constraints are not a good excuse for doing poor design since ... using NULLS will crunch time over the entire system lifecycle. ... IS NULL SQL predicates become almost mandatory. ... As far as I am concerned as a daily database practitionner, ...
    (comp.databases.theory)
  • Re: SQL
    ... > If you design right, you can *shift* much behavior to being data and ... > SQL is close to being Turing Complete. ... It is a bad habit of yours. ... If you're seriously suggesting that CRUD applications are equal ...
    (comp.object)
  • Re: Examples of SQL anomalies?
    ... If you don't spend enough time on design, ... NULLs are only one way to deal with missing information. ... the introduction of flag ...
    (comp.databases.theory)
  • Re: Object-oriented thinking in SQL context?
    ... away from arrays and other non-OO data structures associated ... SQL is constrained to 'trivial' arrays. ... Design: Logical Design", 4th edition. ... Something analogous happens with database design. ...
    (comp.databases.theory)