Re: Database Design Problem

From: Daniel (danny_at_noemail.com)
Date: 04/29/04 

Date: Thu, 29 Apr 2004 10:37:19 -0600

Thanks for the post. I understand your conceptual reasoning however I did
not give you the whole view of my table with constraints/ cascading... Why
not use varchar when you need the flexibility? I have seen many examples of
using varchar!

Thanks again

"Joe Celko" <jcelko212@earthlink.net> wrote in message
news:uH9s1zZLEHA.2400@tk2msftngp13.phx.gbl...
> Close but it needs a lot of work.
>
> >> The problem I am running into is there is another field [sic]
> (Serial) which is a unique to each line item (order details record
> [sic]) for the Order. <<
>
> That is your major conceptual problem. Rows are not records; fields are
> not columns; tables are not files. I want you to learn the (huge)
> differences and make yourself say it out loud when you are writing DDL.
>
> That line number is a PHYSICAL thing on a screen or paper form and not
> part of a logical model. The LOGICAL model is keyed on the order and the
> item; the item has a quantity within the order as a non-key attribute.
>
> Don't you find it amazing that so many things in the real world are
> VARCHAR(50)? Get serious, do the research and do not ever make up a
> data type on the fly.
>
> I will make some guesses to get this thing more reasonable in my
> re-write. You need to add keys, DRI actions, DEFAULTs and CHECK();
> these things are one reason a column is not a field. See why I want you
> to think with the right concepts?
>
> CREATE TABLE Orders
> (order_id CHAR(20) NOT NULL PRIMARY KEY,
> employee_id INTEGER NOT NULL
> REFERENCES Personnel (employee_id)
> ON UPDATE CASCADE,
> customer_id INTEGER NOT NULL
> REFERENCES Customers(customer_id)
> ON UPDATE CASCADE);
>
> Since you have a serial number, I am going to assume that each item you
> sell is unique -- like TV sets.
>
> CREATE TABLE OrderDetails
> (order_id VARCHAR(50) NOT NULL
> REFERENCES Orders(order_id)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> upc CHAR(9) NOT NULL, -- or other industry standard code
> serial_nbr CHAR(15) NOT NULL
> CHECK(..), -- a regular expression?
> customer_po VARCHAR(50) NOT NULL, - these can be weird
> quantity INTEGER DEFAULT 1 NOT NULL
> CHECK (quantity > 0),
> ship_date DATETIME NULL, -- null is not shipped yet
> instructions VARCHAR(250) DEFAULT '' NOT NULL,
> PRIMARY KEY (order_id, upc, serial_nbr));
>
> The product_catagory is an attribute of the upc code. By definition,
> there is no such thing as "??_category_id"; you don't understand what a
> category is (attribute value) and an identifier is. Does this code
> point to one and only entire entity? No, it is a value of an attribute.
> If you want to be really silly, string more postfixes on it, like
> "??_category_id_value" or "??_category_id_value_code" and see how silly
> it is. And it violates ISO-11179-5 rules.
>
> >> I am just wondering is it worth having an Order table or maybe I can
> create one table or map it another way?<<
>
> Depends on how you do business. Is an order a logical thing that you
> have to account for? Or is an order more like an event which is really
> a cluster of individual purchases that came in a the same time?
>
> --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 Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!