Re: Are redundant fields ever appropriate?

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 8 Nov 2001 08:36:25 -0800
Message-ID: <c0d87ec0.0111080836.5df0331b@posting.google.com>

>> I know this is a pretty basic question, but I can't seem to find
an answer anywhere. If I have a few tables like: <<

Please post DDL insrtead of your personal narrative. Even your pseudo-code had problems -- DATE is a reserved word, the octothrop is not part of SQL, etc.

CREATE TABLE Invoices
(invoice_nbr INTEGER NOT NULL PRIMARY KEY,  customer_nbr INTEGER NOT NULL REFERENCES Customers(customer_nbr),  job_nbr INTEGER NOT NULL REFERENCES Jobs(job_nbr),  invoice_date DATE NOT NULL DEFAULT CURRENT_DAT,  ...);

CREATE TABLE InvoiceLines
(invoice_nbr INTEGER NOT NULL

             REFERENCES Invoices (invoice_nbr)
             ON DELETE CASCASE
             ON UPDATE CASCASE,

 line_nbr INTEGER NOT NULL
 part_nbr INTEGER NOT NULL

          REFERENCES Inventory(part_nbr)  quantity INTEGER NOT NULL DEFAULT 0

          CHECK (quantity >= 0)
 price DECIMAL (12,4) NOT NULL DEFAULT (0.0000),   ...,
 PRIMARY KEY (invoice_nbr, line_nbr));

>> Say I wanted to make sorted lookups faster and wasn't much worried
about update speed. Would it ever be appropriate to have a table like:

 InvoiceLines:
 Invoice#
 Part#
 Quantity
 Price
 Customer#  

The reason I'd want to do it would be to make reporting for line items by customer# faster. Is this something I'd ever want to do? <<

No. The foreign key reference will tie the two tables together via pointer chains in most modern SQL products. The invoice number will appear only once in a single special index structure with pointers directly to rows in both Invoices and InvoiceLines. In effect, the table are "pre-joined" . Received on Thu Nov 08 2001 - 10:36:25 CST