Re: Table design issue

From: Joe Celko (jcelko212_at_earthlink.net)
Date: 10/09/04 

Date: Sat, 09 Oct 2004 07:50:53 -0700

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

Is this what you meant, if you had posted DDL? Your Client table is
acutally the history:

CREATE TABLE ClientHistorys
(client_nbr CHAR(4) NOT NULL,
 client_name VARCHAR(50) NOT NULL, -- really that long?
 client_desc VARCHAR(100) NOT NULL,
 client_term VARCHAR(100) NOT NULL,
 start_dt DATETIME NOT NULL,
 end_dt DATETIME, -- null is current
 PRIMARY KEY (client_nbr, start_date);

The current situation is given by:

CREATE VIEW Clients (client_nbr, client_name, client_desc,
 client_term)
AS
SELECT client_nbr, client_name, client_desc, client_term
  FROM ClientHistorys
 WHERE end_dt IS NULL;

CREATE TABLE ClientLocations
(client_nbr CHAR(4) NOT NULL
      REFERENCES Clients (client_nbr),
 area VARCHAR(100) NOT NULL,
 city_id INTEGER NOT NULL,
 country_id INTEGER NOT NULL); -- use ISO standards!!

>> And I have two history tables ClientHist and ClientLocationHist to
record all the changes to the master tables. <<

And you put non-relational IDENTITY and BIT columns in them. Why??

Why didn't you just buy any of several packages that will maintain
change histories for your database? If you pay yourself more than $10
per hour, you'll save money.

>> The fields are separated by , and records are separated by – <<

That is not SQL; that is a display issue for the front end. That is the
idea of a tiered architecture. Also, why do you want to destroy 1NF in
the table?

>> In this case my table structure won't give me the correct result. <<

You have the wrong keys. How do you relate locations to clients? If a
location is a characterstic of a client, which seems reasonable, then
you have split an entity across two tables and need to get it back
together:

CREATE TABLE ClientHistorys
(client_nbr CHAR(4) NOT NULL,
 client_name VARCHAR(50) NOT NULL, -- really that long?
 client_desc VARCHAR(100) NOT NULL,
 client_term VARCHAR(100) NOT NULL,
 start_dt DATETIME NOT NULL,
 end_dt DATETIME, -- null is current
 area VARCHAR(100) NOT NULL,
 city_id INTEGER NOT NULL,
 country_id INTEGER NOT NULL);
 PRIMARY KEY (client_nbr, start_date);

and correct the Current view.

CREATE VIEW CurrentClients (client_nbr, ...)
AS
SELECT client_nbr, client_name, client_desc, client_term,
       area, city_id, country_id
  FROM ClientHistorys
 WHERE end_dt IS NULL;

Then create the prior view:

CREATE VIEW PriorClients (client_nbr, ...)
AS
SELECT client_nbr, client_name, client_desc, client_term,
       area, city_id, country_id
  FROM ClientHistorys AS H1
 WHERE end_dt
      = (SELECT MAX(end_dt)
           FROM ClientHistorys AS H2
          WHERe H1.client_nbr = H2.client_nbr);

Using VIEWs will keep the data correct, while using base tables will
require constant updating and uncertainity. File systems had no virtual
tables, so you are still thinking you ned to have a physical magnetic
tape for eveything.

The "magic numbers" on the VARCHAR(n) columns do hot look like the
results of careful research, but rather quick design. Did you find the
ISO standard codes for countries in your careful research? No
professoional programmer woudl make up codes when ISO Standards exist.
How about city codes? Ever see a person with a fifty character name?
The USPS only allows 35 for a mailing label.

Start over and actually plan each column when write the data dictionary.
You do have a data dictionary, don't you?

--CELKO--
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!