Re: Maybe I didn't explain right

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Vicki C (vcandy_at_hotmail.com)
Date: 03/02/04 

Date: Tue, 02 Mar 2004 22:59:42 GMT

Thanks for your help Joe

But in the Real World I'm working in the, Quoted Orders that we receive
from our customers must be returned with our quoted price in the EXACT
LINE Number Order as the Original "PAPER FORM" that they came in on.

I don't have the luxury of re-educating my bosses or the customers on
how wonderful the world would be if everyone followed the view of the
RDBMS model.

Bottom line is I can use IDENTITY, my program will have to track and
update the line numbers.

The PRIMARY KEY on the Orders table and the FOREIGN KEY/DRI on the Details
table is exactly what I have. I just had trouble with my Line Num
requirement.

I know the kind of reports your talking about, we have received some 20-30
page reports where the original order doesn't matter from the vendor. And
we spend hours+ trying to cross check to make sure we have everything
return to us. It's not that ppl want to mimick a paper form so much as
they need to mimick real life rules (boss/customer) over the "Ideal" RDBMS
model.

Joe Celko <joe.celko@northface.edu> wrote in
news:Oc7g3IHAEHA.1844@TK2MSFTNGP11.phx.gbl:

>>> I need to know how to set up the table.. IDENTITY on Line# isn't
> working...doesn't reset on each Order. ... Can the MSSQL backend keep
> these fields [sic] for me? .. It's just my Details table that is a
> problem to set up. <<
>
> This posting is very confusing, but I think your problem is that you
> have the wrong mental model of what an RDBMS is. Rows are not records;
> fields are not columns; tables are not files; there is no sequential
> access or ordering in an RDBMS, so "first", "next" and "last" are
> totally meaningless.
>
> When you model a classic "order and details" schema, you start with the
> Orders table. It has a PRIMARY KEY, usually called the "Order number",
> which is issued at the time each order is taken. Since the order number
> is exposed to the user, it needs to be self-validating -- a check digit
> or a syntax rule. It should never be an IDENTITY or other unverifiable
> exposed proprietary locator. You have no data integrity right now!!
>
> The order details are in a second table which has a FOREIGN KEY
> reference to the Orders table. There should also be a set of DRI
> actions on this column. The PRIMARY KEY will be the order number and
> the inventory code for each item (UPC, SKU, VIN, GTIN or other industry
> standards are preferred).
>
> It will **never** be a line number!! A line number is a PHYSICAL
> location and has nothing whatsoever to do with the LOGICAL data model.
> You are NOT selling the customer a line number. A line is **field** on
> a screen or a paper form; there are no fields inside SQL. Columns are a
> totally different concept.
>
> What you seem to want to do is mimick a paper form inside a database,
> thus destroying all the advantages of an RDBMS.
>
> Stop what you are doing, and some help or some training.
>
> --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!
>

Relevant Pages

  • Re: Maybe I didnt explain right
    ... If your order detail items are inserted in proper order into the ... > LINE Number Order as the Original "PAPER FORM" that they came in on. ... > The PRIMARY KEY on the Orders table and the FOREIGN KEY/DRI on the Details ... > they need to mimick real life rules over the "Ideal" RDBMS ...
    (microsoft.public.sqlserver.programming)
  • Re: Large Tables
    ... information from your paper form and make separate tables ... with a Primary key using the autonumber. ... keynumber ...
    (microsoft.public.access.forms)
  • Re: Maybe I didnt explain right
    ... It has a PRIMARY KEY, usually called the "Order number", ... location and has nothing whatsoever to do with the LOGICAL data model. ... What you seem to want to do is mimick a paper form inside a database, ... constraints, Declarative Referential Integrity, datatypes, etc. in your ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL to combine columns
    ... (owner_id INTEGER NOT NULL PRIMARY KEY, ... ON UPDATE CASCADE ... It is the foundation of RDBMS, ... But do you want to be a good SQL programmer instead? ...
    (comp.databases.ms-sqlserver)
  • Re: SQL to combine columns
    ... Please post DDL, so that people do not have to guess ... It is the foundation of RDBMS, ... Since, my application is small and does very basic SQL usage, the internet provided all the information I needed, but a book would have answered my question so the might CELKO would not have been bothered. ... (owner_id INTEGER NOT NULL PRIMARY KEY, ...
    (comp.databases.ms-sqlserver)