Thanks for your kind reply, Celko. Your insight into the fact that this system was originally written in COBOL and converted to VB/SQL by the COBOL developer is amazing. Were you once beaten up by a gang of COBOL geeks?
Never put the table name as a column prefix (that was COBOL record definition not SQL).
This particular naming convention is required in our system due to a third-party auditing program that uses triggers to write to an audit table whenever a column value is inserted/updated/deleted. If we duplicate column names between tables, that program doesn't record the audit row correctly.
There is no such crap as a magical genetic “key” (that was a record number in mag tape files or pointer chains in network DBMS, not SQL).
Perhaps it is my fault for over-simplifying the sample data. In reality, the "key" columns in these tables are IDENTITY surrogate keys. I did not create them that way in the sample data because I wanted to control the values to simplify. If "key" is not the correct term to describe a surrogate key, then what is? Are surrogate keys now out of style? Because before we introduced them, users had to make up their own identifiers and they kept hand-written logs or Excel spreadsheets... some of them probably chiseled the identifiers in stone tablets as a left-over from the old COBOL days.
Why did there no DRI or other constraints? (answer: files do not have them).
Again, my fault for over-simplifying. In reality, the tables are loaded with foreign key constraints that enforce data reference integrity.
SQL Programmers use for particular keys; we use a VIN, not a silly invented “autokey”, etc. Let's use the UPC code to identify the Ingredients instead of a silly pseudo-pointer.
Again, my fault for not providing the complete details of our products in an attempt to simplify. We are not baking cakes, we are making wine. Our grapes do not arrive with handy little bar codes stamped on them. I'm pretty sure you're not suggesting I assign a vehicle identification number to my grapes, but my quick Google search on your acronyms didn't give me much of a clue. Could it be that you are referreing to a surrogate key?
The use of camelCase is also out of date, a bitch to read and not ISO.
So if I insert underscores in my column names, I will be able to get the results I need? Who knew that such a simple fix would do the trick. I feel so stupid now.
Why did you do a percentage with DECIMAL(18,5)?
What do you recommend?
Likewise, “ship_recv” looks like a mag tape file bit flag; an SQL programmer would have a received date that is NULL when the shipment is still in process. But wait! This is a redundancy status for input and output to inventory! The sign of the quantity tells us 'R' or 'S' without loss of data integrity, like you HAVE NOW.
Again, S and R were used to over-simplify a process that not only receives and ships, but uses inputs and outputs to blend product and adjust inventory balances (which can be positive or negative numbers, so a negative qty is not necesarily a shipment). I could have left off the S and R but I need a way to identify actual shipments and receipts as opposed to movements and adjustments.
A recipe has many ingredients so that gives us a two column real key instead of a fake mag tape record number. This is a relationship, so we need DRI in this table. But why does the ticket number have any thing to do with baking the cake?
CREATE TABLE Recipes
(recipe_nbr INTEGER NOT NULL,
upc CHAR(13) NOT NULL
REFERENCES Ingredients(upc),
PRIMARY KEY (recipe_nbr, upc),
recipe_pct DECIMAL(8,5) NOT NULL
CHECK(recipe_pct BETWEEN 00.00001 AND 100.00000));
You lost me a bit here. I didn't present a Recipe table, but rather a Receipt table that stores the details of the receipts into the plant (i.e. 75% Zinfandel Grapes from Napa Valley, 25% Zinfandel Grapes from Monterey, etc) The receipt is linked to the inventory transaction via one of those evil "key" columns -- specifically TrnKey.
Now when we get to the Inventory, it looks like you are trying to do a COBOL record structure again, not SQL. In SQL all column are scalar and this looks like you are cramming upc codes 1, 2 and 3 into a single column!!
Besides spitting on Dr. Codd's grave, we have no idea to which of the three ingredients the quantity applies. Also, what is a “GA”; I do not know that unit.
INSERT INTO Inventory_Transactions
(inventory_ticket_nbr, ticket_date, upc, ticket_qty, uom)
VALUES (1, '2011-01-01', '123', 1000, 'GA'),
(2, '2011-01-02', '123', -100, 'GA'),
(3, '2011-01-03', '123', 500, 'GA'),
(4, '2011-01-04', '123', -100, 'GA');
Somehow you have gotten the idea that product #123 is actually referring to ingredients A, B, and C. Think of it this way. Product #123 is 2010 Napa Valley Merlot Wine. On day 1, we received 1000 GA (GA is the UOM for Gallons) and it was sourced from grapes from Farmer Joe in Napa Valley (85%) and grapes from Farmer Sam in Monterey (15%). On day 2, we bottled 100 gallons of product #123 and we must prove the percents in the bottle. On day 3 received 500 GA of product #123 but its grapes were sourced from Farmer John in Napa Valley (95%) and Farmer Dick in Bakersfield (5%). It is all mixed together in the same tank. On day 4 we bottle another 100 GA, but we need to prove the latest percents -- how much from Napa Valley and how much from Bakersfield.
Perhaps the transition to SQL for dinosaur COBOL developers would be easier if their questions were not attacked with arrogance and petty insults.