Re: Soliciting Architecture Opinions

<mjcr_2003_2005_at_yahoo.com> wrote in message news:1124810737.477764.193670_at_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.
>

The question itself begs another discussion, before we even get started.

It's about the difference between the correct model for storing and retrieving data, on the one hand, and the usage of data in exchange on the other.

Just as the enterprise has "customers", so likewise, the database has "customers". The question is not whether the customers of the enterprise know or should know about CustomerNumber but wether the customers of the database should know about that Item of data.

Who are the customers of the database? They are application programmers and the programmers who write them. They are also the analysts who use tools like interactive SQL, or the graphic equivalent, to look up data on the fly. Should there people be expected to understand and use Customer Numbers correctly?

My opinion is yes. I agree with you that #1 is better design.

It's better at the physical level, because the primary key will consist of fewer bytes.
More importantly, its better at the logical level, because it identifies a customer based on an identifier that's under control.

What if a customer changes address? Does that customer acquire a new primary key, and thereby a new identity? How do we tie the purchasing history or the credit record of the "old" customer to that of the "new" customer? It's a mees.

As an aside, customers ARE willing to remember their numbers. When you walk up to a car rental booth at an airport, one of the first questions they ask you is whether you know your confirmation number. And most customers do. Received on Wed Aug 24 2005 - 15:13:52 CEST