Re: Soliciting Architecture Opinions

On 23 Aug 2005 08:25:37 -0700, mjcr_2003_2005@xxxxxxxxx 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

.

Relevant Pages

  • Re: Access the right program for the job?
    ... include any other fields that describe a customer only; ... as the primary key, and get rid of the PartID field. ... CustID (foreign key from tblCustomers) ...
    (comp.databases.ms-access)
  • Soliciting Architecture Opinions
    ... How should the PRIMARY KEY be defined? ... City ... If I was to call my Customer and ask "What is your CustomerNumber?". ... Regards and thanks in advance. ...
    (comp.databases.theory)
  • Re: Access the right program for the job?
    ... include any other fields that describe a customer only; ... as the primary key, and get rid of the PartID field. ... CustID (foreign key from tblCustomers) ...
    (comp.databases.ms-access)
  • RE: Making one field on a form dependent on another fields results
    ... numeric primary key and a text field is a good one. ... control and that it will execute when the AfterUpdate event occurs (whenever ... >> OK, before addressing the combo box, and how to display the information ... >> got to change every Orders record for that customer. ...
    (microsoft.public.access.forms)
  • Re: 2nd try: have default items on every report
    ... We are selling plants to customers. ... many relationship between the Customer Info Table and the Orders Table. ... TrackingNumber <primary Key> ... They want the default 60 items printing on every report even ...
    (microsoft.public.access.forms)