Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Is the use of VARCHAR(256) as Primary Keys preferred in Oracle?

Re: Is the use of VARCHAR(256) as Primary Keys preferred in Oracle?

From: Alex Filonov <afilonov_at_yahoo.com>
Date: 29 Nov 2002 21:38:25 -0800
Message-ID: <336da121.0211292138.403c7e32@posting.google.com>


Galen Boyer <galenboyer_at_hotpop.com> wrote in message news:<u1y534yh1.fsf_at_hotpop.com>...
> On 29 Nov 2002, afilonov_at_yahoo.com wrote:
> > Galen Boyer <galenboyer_at_hotpop.com> wrote in message
> > news:<uk7iyc0bt.fsf_at_standardandpoors.com>...
> >
> >> On 27 Nov 2002, ctcgag_at_hotmail.com wrote:
> >> > I would rather tell someone "Let's pretend you SSN is
> >> > 000-00-0001 until we figure this out" than to amputate the
> >> > wrong person's leg.
> >> >
> >> > I'm not arguing against the use SIDs, I use them all the time.
> >> > I just think that you if you use a SID as the pk, but then
> >> > still have a natural key as a unique and not null field and
> >> > write the application accordingly, you probably aren't solving
> >> > the problems you think you are solving. If you are writing the
> >> > application so the the natural key is not required to be unique
> >> > and not null, then why have that constraint in the database?
> >>
> >> This is the point. When I started coding, I "thought" ssn's were
> >> unique. I then find out they aren't after the app is in
> >> production. With the SSN as the PK, I have a much bigger issue.
> >> I now have this key all over the database, and it is the
> >> incorrect key and will need to be changed.
> >>
> >> If I had a dummy key, then I would be able to isolate the issue
> >> to how to handle the SSN number. If in fixing it, like your
> >> example talks about, I might amputate the wrong leg, well, then
> >> how I deal with and fix this issue would be different. I might
> >> not fix it right away, but instead, tell the guy (as you said,
> >> lets pretend your ssn number is 000-000-0001 until we figure this
> >> out). But, when it came time to fix it, it would be an isolated
> >> fix. Maybe an extra piece of code added to the app to count how
> >> many rows returned and then prompt for blah... In your scenario,
> >> this same exact fix might be your solution as well, but you would
> >> have the added task of redefining your PK. So, not only do you
> >> have to implement the same fix I would have to, you also have to
> >> fix your database in a drastic fashion.
> >
> >
> > This is quick fix vs redesign question. If you prefer to have a
> > system where you can do quick and dirty fix, you are right. Damn
> > that person's leg. You're not gonna be sued for it anyway.
>
> You missed my point. Both of us would have to come up with a fix for
> the duplicate SSN numbers, and both of us would have to make sure that
> we don't cut off the wrong leg, but, after this was figured out, you
> would still have to completely redesign your primary keys, while I
> wouldn't.
>
> > If your initial design assume some logical PK, all your code is
> > written based on this assumption. Dummy key just helps you to do quick
> > and dirty fix. So for the outside world your software just pretends to
> > work. To make clean permanent fix you need to redesign your system
> > anyway.
>
> Both of us would be looking for and implementing a quick fix. Our
> long-term fixes are what would be drastically different. Mine would be
> implementing the new algorithms around the duplicate SSN numbers, ONLY.
> Yours would include that and a PK redesign.

Honestly, I don't see why redesign in case of dummy keys is easier. Logical design is based on natural keys anyway. All algorithms and queries are based on logical design, not phisical one. There is some difference if this particular PK is used as FK in some other table(s). But, in case of natural keys, system won't allow me to put a duplicate SSN intil I fixed design and software. In case of dummy keys, there are 2 possible outcomes.
1. There was unique key on SSN. Attempt to enter duplicate key is rejected. Quick and dirty fix accepted: remove unique key on SSN. Next time wrong person's leg is cut off. You start real redesign of the system.
2. There was no unique key on SSN. Wrong person noticed leg cut off after operation. You start real redesign of the system.

There is still no way around redesign. Received on Fri Nov 29 2002 - 23:38:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US