Soliciting Architecture Opinions

From: <mjcr_2003_2005_at_yahoo.com>
Date: 23 Aug 2005 08:25:37 -0700
Message-ID: <1124810737.477764.193670@g44g2000cwa.googlegroups.com>

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

..... 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.

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.

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.

How would you define the primary key?

When is it appropriate to use a system generated primary key?

Regards and thanks in advance. Received on Tue Aug 23 2005 - 10:25:37 CDT