From: AC

To:  Editor

 

While I have you there, can I get your opinion on a basic question like primary key. I do them like this - but not many others agree with this.

 

CLIENTS

CLIENTID CONAME                  FIRSTNAME SURNAME

===================================================

GOLDE    Golden Valley Mines NL  Warren    Beckwith

ROBER3   Robe River Mining       Peter     Becu

COMPNS   Compaq - NSW            Julian    Bedell

 

On thing I like is having a meaningful key in other tables

 

CLIENTINVOICES

INVOICEID CLIENTID CATEGORYID INVOICETYPE

==========================================

2         KEYS     Inv        Product

3         HIH      Inv        Both

4         PHOTE    INV        Time

5         ALCOH    INV        Time

 

 

From: Fabian Pascal

To: AC

 

One thing you got to understand is that whether a certain design-- including choice of keys--is "right" can only be established relative to a set of business rules/dependencies. That in turn requires thorough knowledge of the business reality that is being represented. It is, therefore, difficult to say much about a bunch of key columns in the abstract.

 

What exactly is your question? It's not clear from your message.

 

 

From: AC

 

I’ll try again. In a customer order application would you make the Client/Customer table CHAR6 or INTEGER?

 

 

From: Fabian Pascal

 

You must keep the data types (domains) and their representations distinct in your mind and not confuse them (see Chapter 1 in my book).

 

It is obvious that numeric operations cannot be applied to values of type ID, so logically the domain is alphanumeric. However, the physical representation can be anything, and is subject to optimization. In a truly relational system that fully supports user-defined domains, you could choose a numeric representation and restrict the logical operations to only those meaningful for IDs. Unfortunately, SQL products do not really support domains, let alone user-defined ones.

 

 

From: AC

 

OK, but which one would you choose in a typical customer order app? CHAR6 or INTEGER?

 

 

From: Fabian Pascal

 

The point I was trying to make is that with current products this is an implementation detail, not a general design issue and, therefore, it would vary with each product, implementation and situation.

 

 

From: AC

 

Thanks for the response - but I am still unclear what your opinion is. I asked a couple of other people here and they said, "I am not sure why he doesn't want to answer this question more specifically?"

 

I think you are saying what the customer asks for. Is this correct?

 

 

From: Fabian Pascal

 

What I said was that you are asking a question which cannot be replied generally in the abstract. So it's not a matter of me "not wanting to answer”: anybody who does answer, does not understand database fundamentals, which is true of almost all practitioners. So the problem is not with my answer, but with those around you.

 

No, I am not saying what the customer wants goes. Customers should never be exposed to implementation details, they should only deal with the logical model (here, domains, not representations).

 

 

From: AC

 

I understand that, but I am still none the wiser. You haven’t given me any reasoning when you might choose one, and when you might choose another

 

Is it still abstract when I have given you a specific example?

 

 

From: Fabian Pascal

 

Becoming wiser is not done via email recipes--what I call the cookbook approach: how would you do this?-- but from long-term study of fundamental principles and concepts. That is what you lack and why you don't accept my answer. You are asking me to teach you fundamentals via email -- an impossible task. Set up my seminar and then you'll see what I am talking about.

 

Posted 01/03/03

 

 

 

[ABOUT] [QUOTES] [LINKS]