Re: Best practice kinda question

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: --CELKO-- (remove.jcelko212_at_earthlink.net)
Date: 12/05/04 

Date: Sat, 04 Dec 2004 17:09:38 -0800

>> But should I add CompanyID to every table that will be related to
Company? <<

Yes, of course. How else would you know that it is, in fact, related to
the company? Dr. Codd's rules? All relationships are shown by values
in columns of tables.

Your skeleton schema had some serious errors.

Was the computed totals column in invoices redundant or do you really
price by the invoice in a manner unrelated to the items purchased? Did
you really want all of the MONEY datatype problems?

Are you one of those newbies who thinks that IDENTITY "id" columns are a
replacement for a key? Sorry, it is a way to destroy data integrity and
mimic a magnetic tape file system.

You showed a vague magical "id" in Invoices, but it never appears in
Invoice items. I'll bet you changed data element names from table to
table, which will destroy the data dictionary. Please read any data
modeling book or the ISO-11179 Standards.

Here is a new skeleton:

CREATE TABLE Customers
(cust_id INTEGER NOT NULL PRIMARY KEY,
 name VARCHAR(35) NOT NULL,
 ..);

CREATE TABLE Invoices
(company_id INTEGER NOT NULL
    REFERENCES Companies(company_id),
 invoice_nbr INTEGER NOT NULL,
 PRIMARY KEY (company_id, invoice_nbr),
 cust_id INTEGER NOT NULL
    REFERENCES Customers(cust_id),
 ..);

CREATE TABLE InvoiceItems
(company_id INTEGER NOT NULL,
 invoice_nbr INTEGER NOT NULL,
     FOREIGN KEY (company_id, invoice_nbr)
     REFERENCES Invoices(company_id, invoice_nbr),
 product_id INTEGER NOT NULL, -- refs Inventory
 amount DECIMAL(12,4) NOT NULL,-- not priced from Inventory?
 PRIMARY KEY (invoice_nbr, product_id));

CREATE TABLE Companies
(company_id INTEGER NOT NULL PRIMARY KEY, -- DUN number?
 company_name VARCHAR(35) NOT NULL);

--CELKO--
Please post DDL in a human-readable format and not a machne-generated
one. This way 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!

Relevant Pages

  • Re: Assign a Primary Key in Query
    ... CREATE INDEX PK_Invoices ON Invoices WITH PRIMARY ... don't have indicate "DISALLOW NULL" since Primary keys are NOT NULL by ... In ANSI 92 SQL: ... > What is the proper SQL to create a primary key within a query? ...
    (microsoft.public.access.queries)
  • Re: Postpone creation of attributes until needed
    ... create table b, create pseudo column on table a, link to column on ... Assume a Customers table and an Invoices table. ... Users will never see the primary key, ...
    (comp.lang.python)
  • Re: One to One relationship
    ... assuming that the autonumber field is the primary key in the "parent" table. ... You should be able to use the AcctID ... The invoices can only have one expense ...
    (microsoft.public.access.tablesdbdesign)
  • Record Order
    ... I made a database for all of the invoices that come ... For a Primary Key, I used a field called ID, with AutoNumber for the data ... Is there a way to use AutoNumber for this safely? ...
    (microsoft.public.access.tablesdbdesign)
  • Re: How do I discover a tables primary key?
    ... sp_helpconstraints system procedure in isql the result looks like ... an index once I have an entry in sysindexes. ... -- Number of references made by this table: ... from within my program I only get back one row, the PRIMARY KEY ...
    (comp.databases.sybase)