I hope you know that IDENTITY can never be a key, that it has no check
digits so you cannot even validate it. You would never use it for
anything a customer has to see and use.
Can you explain the logical differences among "SaleID" (which, being
IDENTITY, is the physical location in storage of a row), a "SLSaleID"
and a "SLPSaleID"? Why is "SaleLineEquipItemID" logically different
from "SLEquipmentID"? Ditto with almost everything else you have posted.
Since they have different names, they **must** be logically different.
Surely, you would never, never name a data element for each table where
it located.
Naming data elements by the file they came from was how we had to locate
COBOL records. I hope that "SalesLine" does not refer to the PHYSICAL
sales form's printed lines on a piece of paper or a screen. The next
worst thing is prefixing the datatypes to data element names -- that is
BASIC programming.
If you had followed ISO-11179 standards for names, used DRI, and had
real keys, would the schema look more like this?
CREATE TABLE Sales
(sale_id INTEGER NOT NULL PRIMARY KEY
CHECK(<< validation code >>),
customer_id INTEGER NOT NULL,
sale_date DATETIME NOT NULL);
CREATE TABLE Equipment
(sale_id INTEGER NOT NULL REFERENCES Sales(sale_id),
equipment_id INTEGER NOT NULL,
PRIMARY KEY (sale_id, equipment_id));
CREATE TABLE Parts
(sale_id INTEGER NOT NULL,
equipment_id INTEGER NULL,
part_id INTEGER NOT NULL,
FOREIGN KEY (sale_id, equipment_id)
REFERENCES Equipment(sale_id, equipment_id),
PRIMARY KEY (sale_id, equipment_id, part_id));
how do I get the NEW SaleLineEquipItemIDs? <<
You don't have them in the first, if you know how to design an RDBMS,
instead of a sequential file system.
When get an order for equipment you have assembled before, you look up
the sales for that customer, you get the (sale_id, equipment_id), and
then the parts with that key. When you do the two insertions, you go to
the sales numbers table or routine and use it in both the Equipment and
Parts table.
Now this is a trival insertion procedure. 80% or more of the time that
you have complex DML, it is due to bad DDL. Bad DDL is usually attempts
to imitate a file system or an OO model in an RDBMS.
--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.
*** Sent via Devdex
http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!