| |
Jot and Jab wrote:
>
> In lugnet.db.brictionary, Larry Pieniazek writes:
> > Yes, indeed, John is (as evah) right. Let me weigh in here as well...
> >
> > Putting meaning in keys is a sign of not very well seasoned designers,
> > IMHO.
> >
> > A pretty strong principle of database design is NOT to let your key have
> > business meaning. Get keys from a source that is intended to be unique,
> > then never ever ascribe meaning to them. All meaning should be in
> > attributes.
> >
> > Else your business logic will be tres brittle and you will live to
> > regret it. Don't do it. Trust me.
>
> Pardon my inexperience. I am neither a program designer, nor a person with any
> business logic whatsoever...perhaps I should have footnoted these factss before
> sticking my idea out here.
>
> I think that 90% of the users of a would-be PartSearch.com (or whatever) are
> NOT businessmen, NOT out to make cash from this, but simply ignoramuses like
> myself who would potentially benefit from a more logical numbering sytem that
> might enable them to track down the parts they're trying to find.
Whoops. Talking in shorthand tripped me up. In system-architect-speak
"business" logic is the
actual desired work effort of the program (contrasted with non business
logic which is the infrastructure or other code that you have to do in
order to get to the good stuff), the "good stuff" as it were.
For example: Consider a button on a screen that when pressed will
display an image of whatever part most closely matches the current
pulldown choices (shape, number of studs, thickness, etc) on the screen.
There is non business logic all over the place... the code that realizes
you pressed a button, the code that grabs the parameters from the screen
and checks that they are valid, the code that, after the query is built,
submits it to a database (and all the code inside windows itself, or
inside the database itself) is all non business logic. It's just
plumbing.
Only the code, in this contrived example, that does any "reasoning"
about which part actually is the closest match from the ones that were
returned is "business logic".
Note that we never said a thing about dollars and cents or businessmen.
Business logic is the important stuff of the program, no matter what the
domain of the program is, be it building bricks, building rocketships or
building portfolios.
Good architectural principles tell you to keep your business logic
separated from all the plumbing infrastructure stuff. Mixing business
meaning (that is, the meaning from the domain you are working in, in
this case interesting facts about the parts themselves) into keys is an
elementary no-no. Keys are plumbing. They should not have any intrinsic
meaning.
To drive this point home, suppose we were building a database of all the
citizens in the US. We need to assign a unique primary key to each and
every citizen. Is the citizen's social security number a good key to
use?
NO! There is no guarantee that it is unique. Duplicate SSNs have already
been assigned, and there is talk of starting to reuse numbers as we are
running out. There is also (or was) business meaning to the SSN as the
prefix digits spoke to where the number block was originally allocated.
Only a naiive DBA would use SSN as a unique key.
Well, how about their driver's license number?
Again, no. Once again it is not unique, different people in different
states have the same number. The number does not stick with a person
forever. I had the same number in FL as I did in NY, but now in MI I
have a different one. (1)
How about if we make a composite key with both the state code and the
DLN? That might be unique.
But now you have even MORE business meaning in the key. Bad. Also you
have a composite primary key which is also a bad idea for a table that's
not a relationship table.
Best is to assign a unique, program derived key to each person. Then
NEVER EVER tell anyone outside the program what it is. It's plumbing and
it is best left invisible.
Lego Part numbers are not unique. Tacking on color codes may possibly
make them unique but probably not, and violates the composite primary
key guideline.
Generate an internal key. Make the part number, the color, and all the
other attributes values of the part, index them to allow fast searching,
but do NOT make them the key.
End of lecture.
Helps?
1 - actually the same one as the last time I lived here, which was a big
hassle to find, due to MI using the DLN as a unique key within their
internal systems. Shame on them. But it's the government, what do you
expect? Competence???
--
Larry Pieniazek larryp@novera.com http://my.voyager.net/lar
- - - Web Application Integration! http://www.novera.com
fund Lugnet(tm): http://www.ebates.com/ Member ref: lar, 1/2 $$ to
lugnet.
NOTE: I have left CTP, effective 18 June 99, and my CTP email
will not work after then. Please switch to my Novera ID.
|
|
Message has 1 Reply:
Message is in Reply To:
 | | Re: wishing upon a star
|
| (...) Pardon my inexperience. I am neither a program designer, nor a person with any business logic whatsoever...perhaps I should have footnoted these factss before sticking my idea out here. I think that 90% of the users of a would-be (...) (25 years ago, 26-Aug-99, to lugnet.db.brictionary)
|
22 Messages in This Thread:
 
 
- Entire Thread on One Page:
- Nested:
All | Brief | Compact | Dots
Linear:
All | Brief | Compact
This Message and its Replies on One Page:
- Nested:
All | Brief | Compact | Dots
Linear:
All | Brief | Compact
|
|
|
|
