Re: Table Design In General

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 Received on Wed May 18 2005 - 13:29:00 CDT