I am a programmer with extensive exposure working with Oracle
databases. However, up until now, I have been working with databases
that were either designed without my input, or for other applications.
This is my first forray into the actual design of the database.

I have been doing some reading and am just starting to grasp the
concept of using a "dumb" primary key in combination with a unique key.
I understand the theory, but am not 100% sure on the practice.

How does one go about working with tables that are linked via "dumb"
keys? Is the best practice to create a View that gives you all the data
you need to see, and edit it accordingly?

As a newbie to this process, I appreciate any time you may take to
respond. If you can point me to any online literature that would also
be appreciated.

Thanks in advance,

Chris

"Chris" <ctaliercio (AT) yahoo (DOT) com> wrote

Be ready for some strong opinions on this one.

I'd suggest obtaining one or both of the following classics:

David Hay -- Data Model Patterns: Conventions of Thought (ISBN:
0-932633-29-3)
David Barker -- Case*Method: Entity Relationship Modelling (ISBN:
0201416964 )

Also, look over some of the articles at Dave Hay's site:
http://www.essentialstrategies.com/

++ mcs

Thanks Mark. I will definitely be checking these out.

Chris

By "dumb" primary key, do you mean a pseudo-key?

Re: the fence Mark hinted at, I fall on the side of not liking
pseudo-keys. I'm not fanatical about it. Use them when they make sense,
when they truely serve a purpose beyond saving a few bytes per row.

Re: using views, If you are going to "edit it accordingly", then you
need an updateable view. This is something that must be designed
carefully.

One question for you: what happened to your DB designers? How about
asking them for guidance? ( but still ask here to get differing
perspectives too)

Ed

Chris wrote:
The best advice is to not use surrogate keys if there are natural keys
that define the record. This is not to say that surrogate keys are not
necessary in some contexts but the SQL Server/MS Access type of attitude
of autonumbering everything by default has nothing to do with best
practice in an RDBMS. The secret is to know the difference between data
that is a natural key and data that is "most of the time" a natural key.

That said there is no value in creating a view unless there is a
business reason to do so. Just creating a view to denormalize should
not be default behavior.

PS: I definitely recomment the books previously mentioned.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

Ed,

Thanks for the input. I am most definitely in contact with the DBA who
will be over-seeing the database. He is in favor of the "dumb" key
concept. here is the reasonig behind our logic.

In this industry (music & entertainment distribution), the
manufacturers assign a "part number" to their products as a way of
uniquely identifying them. In our own product table, if we were to use
that part number as the intelligent key to our product table, we would
run into a problem because the part numbers actually change (more often
than you would imagine).

So - in this scenario - if I have populated the intelligent "part
number" on all of the dependent tables, when the manufacturer changes
this part number onb us, it would be a lot of work to go and update all
the dependnt tables in addition to the main product table.

If we were using a "dumb" key (naming semantics here are irrelevant -
it could be SKU, ID, whatever) that was simply a "next-up" number for
each entry made into the product table, we no longer have a problem. If
the manufacturer changes the part number, the dependent tables are no
longer affected because they are related through the dumb key.

My confusion in the application is this: suppose I want to display the
contents of a location in the warehouse, as follows: Location,
Manufacturer ID, quantity. I can use a simple join to get the
manufacturer ID from the product table and marry it to the data from
the location table. But now, what if I want to actually change the
product in a given location to a different product - it gets a bit more
tricky - especially without the benfit of GUI and the windows control
sets. I am working on a UNIX platform using dumb terminals in the
warehouse.

I hope this makes sense, and I hope that I have a good grasp of the
dumb/psuedo-key concept.

Please feel free to correct me if I am wrong.

Thanks agains,
Chris

Chris wrote:
Darn! My Elvis LP just turned into a widget!
Part numbers don't change.

--
Regards,
Frank van Bortel
Old golfers don't die - they just loose their balls.

Frank,

While I can definitely appreciate the humor in your post, perhaps you
are not familiar with the recording industry ...

Let me most assuredly inform you that in this industry part numbers can
(and do) change all the time. For example:

Record label A manufactures and distributes Album X from artist Y -
part number is 123 and UPC is 789.

Record label B acquires/buys/whatever record company A. Album X by
artist Y is now part number 456 from company B with the same 789 UPC
code (unitl they run out of inventory, at which time the UPC will
change to reflect the new vendor if they reprint the title).

Again - this is just one tiny illustration of an EXTREMELY common
event.

I may not be a DBA, but I certainly know my business.

Thanks,
Chris

"Chris" <ctaliercio (AT) yahoo (DOT) com> wrote

[ Will do database design work for concert tickets ]

Chris wrote:
You just solved your problem: the primary key
did not change:
Company - part# - UPC #

In other words: My Elvis album is still Elvis,
and my Deutsche Grammophon Beethoven s 9th by
the Berliner Philharmoniker, conducted by Herbert von Karajan
is still that, and will always be.
--
Regards,
Frank van Bortel