Re: Are redundant fields ever appropriate?
Date: 8 Nov 2001 08:36:25 -0800
Message-ID: <c0d87ec0.0111080836.5df0331b_at_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 - 17:36:25 CET