Quote:
[snip]
My favorite type of discussion...everybody's got an opinion, and they're
all valid, and do we get e{*filter*}d about them, or what.
Quote:
> Good start of a discussion on philosophies. The decision impacts me often as
> I develop new dbs often, not just maintain one.
> 1a - you could build a single field, text primary key with multiple
> identifier characters instead of concatenated fields forming one pk
> Disadvantage -
> if your data is really multiple fields making up one long text value, you
> lose atomicity and you have to use code to cascade changes, deletes, lookups
> > key, multiples may in fact exist (ie 2 John NMN Smiths). Larger
> candidate key fields which duplicate are not good candidates. However, in
> cases of what categories do you sell?
> "beverages"
> "candy"
> such a candidate would not have duplicates. Notice, non coded, using full
> text and therefore slower. Notice, if end user is a participant and not a
> "hands off" person (typical of Access users but not typical of Oracle
> users), the key is visually identifiable and relevant (I think that breaks
> one of the rules).
> > 2) use a primary key which is meaningless to anything but the database
> > (ie autonumber)
> > Problems: uniqueness of data must be maintained in other fashions (ie
> > an additional index, validation etc).
> Yes, what some people do in Access is to have two "unique indexes" in the
> table. One is the autonumber PK and used for mechanistic queries, the other
> is visual and relevant and used for end user queries.
> The concatenated unique key is also used to enforce uniqueness. Works for
> "candy" example, "John Smith" does not work unless you put in "John Smith1"
> vs "John Smith2"
Exactly. I'm too lazy to use concatenated keys and you still have too
much likelihood of duplicates, unless you add a date/timestamp to it of
some sort. You end up with a concatenated field from something like
First Name, Last Name, Address, Phone Number, but what happens if Joan
Smith comes in with her daughter Joan Smith and they live at the same
address? Since women don't usually use "Junior" or "III", you have a
problem. You end up adding something like "First Data Entry Time" to
make them unique. That's too much typing in a query for me to do. Good
time for an arbitrary unique identifier. But, that's just my opinion...
Quote:
> > Querying criteria is based on non-PK fields (ie John Smith instead of
> 12345).
> Regardless of design, if you unique keys are 1234 or MSACC, the end user
> will probably still want to search for "Microsoft Access" ?
Won't they, though. :)
Quote:
> > I have also heard that
> > autonumbers are not OO, since they expose the workings of the db, but
> > since their purpose is only to generate a unique number, I think the
> > argument is pretty specious[sp].
> Do you mean OOP or OOdb?
Objects either exist or they don't, so there is no "sequence" to their
existence, and no order. Both sequence and order are on another next
layer, conceptually. However, the databases I use append sequentially,
regardless of the quantity of records being appended, so a sequence is a
functional concept. IIRC, SQL7 has a unique type field which is a GUID,
and an identity field with a seed value. The unique would be good,
except I'm too lazy to type them, so I'd rather use the identity field &
seed to emulate an autonumber.
Quote:
> > Since I have mixed applications, I use one or the other depending on
> > circumstances: If there is an easy way to use a "logical" key, and the
> > number of key fields is very small (less than 4) I use the logical key,
> As said above, end user participation in making table joins with 4
> concatenated key fields is problematical.
> > otherwise, I use the autonumber approach, mimic-ing it in Oracle via
> > sequences and triggers as necessary.
> > It took me almost two weeks to explain to an Oracle dba that a
> > twelve-field primary key was counterproductive, but after I pointed out
> > that twelve-field key would be a foreign key on three different
> > three-field to six-field tables and the volume of records was 1:1200 (1
> > record in the table with the twelve-field PK, and 1200 in the
> > three-field table), the dba began to understand the benefits of a
> > "autonumber" PK.
> I had an "in your face" discussion with a class participant (and he happened
> to be the boss of several others) who would not back down on using
> autonumber, while I was angling the discussion at participative users. His
> background was COBOL and Progress(?) and we both agreed to see the other
> person's point. <grin>
> Ananda
> http://www.*-*-*.com/
Yah, the dba I spoke with wanted the logical key until she realized that
the size of one 4-million-&-counting-row table would quadruple (or more)
using it. We were on an older development server at the time, and 10 gb
was all we had to play with. When I brought the tables in from Access97
the size jumped from something like 3.7gb to around 6gb, and we needed
to conserve space for a while. Not to mention Access97 not being able
to use tables having indexes comprised of more than 10 fields (also
IIRC), and the front ends remain in Access.
Isn't this fun?
Metra
Relationships, Sheep and VB programmers