Re: Why does 1:3 relationsihp require another table?
From: AlexS (salexru2000NO_at_SPAMsympaticoPLEASE.ca)
Date: 05/15/04
- Next message: Steve Kass: "Re: Constraint doesn't work as expected"
- Previous message: Daniel Di Vita: "Re: Many-To-Many Realtionships and best Model?"
- In reply to: Steve Kass: "Re: Why does 1:3 relationsihp require another table?"
- Next in thread: Steve Kass: "Re: Why does 1:3 relationsihp require another table?"
- Reply: Steve Kass: "Re: Why does 1:3 relationsihp require another table?"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 14 May 2004 20:06:24 -0700
Steve,
I like the story,
but I heard once
- when high-school grad comes to college he/she hears: forget what you were
taught in school and listen here
- when college grad starts in university he/she hears: forget what you were
taught in college and listen here
- when university grad starts at job he/she hears: forget what you were
taught in university and listen here
- when boss comes: forget everything and do as I say. So, MVP might not
help.
HTH
Alex
"Steve Kass" <skass@drew.edu> wrote in message
news:eyQy02gOEHA.1160@TK2MSFTNGP09.phx.gbl...
> Rick,
>
> While "it's a static requirement" is up there with "the check is in
> the mail," what you've done doesn't sound bad to me (there is one little
> gotcha I'll mention later). The previous passwords are definitely
> ordered and less like a "set" than most things that should be
> normalized. I don't see any real benefit to a second table, and no
> matter how you do it it will seem kludgy. Since you never have to leave
> the current row to deal with a user's password change, there would be
> none of the usual benefit of indexes and constraints. In fact, the way
> you've done it, you can enforce the requirement with a simple table
> constraint:
>
> create table Users (
> userID int not null primary key,
> passwd varchar(20),
> expires datetime default (getdate() + 90),
> oldpass1 varchar(20),
> oldpass2 varchar(20),
> oldpass3 varchar(20),
> check (passwd not in (oldpass1, oldpass2, oldpass3))
> )
>
> And while it's not the prettiest code, the update code is probably nicer
> than what you would need with a "normalized" model:
>
> create proc UpdatePass (
> @userID int,
> @passwd varchar(20)
> ) as
> update Users set
> oldpass3 = oldpass2,
> oldpass2 = oldpass1,
> oldpass1 = passwd,
> passwd = @passwd,
> expires = default
> where userID = 1
> go
>
> I'm interested to hear other replies, but for now, feel free to tell
> them "an MVP told you it was ok." ;) If the requirement changes to 5 or
> another number, add or remove columns, and in fact, one thing you could
> do now is vertically partition by creating a separate table for the
> UserID and the same four password columns you have now. There's no need
> for all these passwords to spread your user table out, and joining on
> unique indexes is no problem when you need to deal with passwords.
>
> Here's the gotcha: Your boss finds out that what everyone in the company
> does is change their password 4 times in 10 minutes so they can keep the
> same password forever. Now he or she changes the rules, and no one can
> reuse any password they've used within the last 90 days. Now you really
> do need another table, and the changes might be a little easier to make
> with a different model. I'd still be tempted to stick with what you
> had, and hope changes won't get out of hand.
>
>
> -- Steve Kass
> -- Drew University
> -- Ref: 43C5E672-952D-48CD-9F5A-1F5B34E67A2A`
>
>
> Rick Scott wrote:
>
> >Preface: I am relatively new to DB design.
> >
> >I have built an intranet for my company from the ground up using SQL
Server 2000. One of the tables is name simply "users". The users table has
the things you would expect, including a PK of userid, a username and a
password, as well as a date the password was last changed (have to change it
every 90 days).
> >
> >Recently, my boss told me that when they change their password, it can't
be the same as the previous 3 passwords. I asked if this might be changed to
5 in the future and was told that 3 is all the company uses for the main
software they use so its a static requirement.
> >
> >Keeping that in mind, I just added 3 columns: old_password_1,
old_password_2 and old_password_3 to the existing users table. I was
instantly accused (by coworkers, not the boss) of having "flat tables" and
not knowing anything about DB design at all. I asked for justification for
this accusation and got nothing better then "because that's how its
done!!!!1"
> >
> >I don't accept those kinds of answers. In order to make this
"relational", the new table would have to have not only a copy of the
user_id (of course), but also an changed date. With my current setup, each
user has only one changed date and doesn't needlessly duplicate data.
> >
> >Outside of the fallacious "But you never know what will happen" argument,
why is it so bad to have flat tables in this particular situation?
> >
> >
>
- Next message: Steve Kass: "Re: Constraint doesn't work as expected"
- Previous message: Daniel Di Vita: "Re: Many-To-Many Realtionships and best Model?"
- In reply to: Steve Kass: "Re: Why does 1:3 relationsihp require another table?"
- Next in thread: Steve Kass: "Re: Why does 1:3 relationsihp require another table?"
- Reply: Steve Kass: "Re: Why does 1:3 relationsihp require another table?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
