Re: Soliciting Architecture Opinions

On 23 Aug 2005 08:25:37 -0700, mjcr_2003_2005_at_yahoo.com wrote:

>I have been lurking on this board for a few months now and have found
>the majority of information rather informative.
>
>Let me start with "Thanks".
>
>My intentions are not to start a "design war" but I am struggling with
>this based up all the conversations that go on regarding the handling
>of the PRIMARY KEY of table.
>
>Some are strong proponents of a system generated key while others are
>opponents.
>
>So here is my question:
>
>Take a very basic entity that exists in every business... "The
>Customer"
>
>How should the PRIMARY KEY be defined?
>
>Should it be:
>
>#1
>
>Table Def
>===============
>CustomerNumber
>Name
>Address
>City
>State
>Zip,etc,etc,etc
>
>Primary Key
>=============
>CustomerNumber

     Generally, something like that.

>..... or should it be .....
>
>
>#2
>
>Table Def
>====================
>Name
>Address
>City
>State
>Zip,etc,etc,etc
>
>Primary Key
>=============
>Name, Address, City, State, Zip
>
>In my opinion it should be #1 but based upon my interpretation of all
>the conversation that occurs here using a system generated
>CustomerNumber strays from the basic principles of db design because
>what really is a CustomerNumber in regards to this entity.
>
>If I was to call my Customer and ask "What is your CustomerNumber?".
>They would have no idea what I was talking about.

     So look it up. Just because the PK is a customer number does not mean you can not look up the customer another way. 'Give me a list of all customers in the northwest of the city with the word "Systems" in their names.'

     Or 'What customers have the phone number "360-876-5432"?' Note the plural. It is possible.

>However, using the second example the composite primary key of Name,
>Address, City, State, Zip uniquely identifies this customer however
>replicating and maintaining these attibutes across all my other
>entities would be a nightmare.

     Suppose John Smith and his son, also John Smith, live the same address.

>While at the same time it uniquely identifies the customer and if I was
>to call my customer and ask "What is your name,address,city,state, and
>zip?". They could answer.

     "Do you want the address of the main office, the purchasing office, the receiving office, or the office that will be using the item? Oh, and I telecommute. And I keep forgetting the ZIP code."

>How would you define the primary key?
>
>When is it appropriate to use a system generated primary key?
>
>
>Regards and thanks in advance.

Sincerely,

Gene Wirchenko Received on Tue Aug 23 2005 - 18:57:59 CEST