Re: Doubling the order

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 08/13/04 

Date: Fri, 13 Aug 2004 11:45:58 -0500

The suggestion I would make would be that you don't use a system generated
value for the orderid at all. Rather you have some process generate a new
order number that you you will present to the user. The difference between
what I would suggest versus Joe, is that I would have both a user built key
AND an identity value. I use identities as hidden keys, and natural keys
and visible ones. But it is important to have some other values that tell
you of an orders uniqueness. Take your table:

> 1.ORDERID - IDENTITY(1,1) - Primary key
> 2.ORDERDATE (getdate() by default)
> 3.USERID (int)
> 4.STATUS (tinyint)
> 5.WAREHOUSEID(int)

Other than the orderId, there is nothing to say that two orders are the
same. The orderdate would change slightly, the userId would be the same,
the status also, as well as the warehouse. You might want to build a
trigger that included line items, and if the line items are exactly the
same, and everything else is the same, then stop the second order. If your
order number was not automatically generated during the insert, but was a
value calculated not in the database level, then you would be passing this
value in during the creation of the order. You would not get duplicates
because the system would disallow that.

The best thing for you to do to solve your immediate problem is use profiler
to capture everything that is going on and compare the trace to what is
expected. If youi get two orders where you should likely have only gotten
one (this will require logging from your UI, since unless you have business
rules against people spending money with you, you likely won't stop anyone
from actually ordering twice!) Most likely you are suffering from a UI
issue where multiple orders are being sent because of poor code. 

-- 
----------------------------------------------------------------------------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services.  All other replies will be ignored :)
"simon" <simon.zupan@stud-moderna.si> wrote in message
news:OPpRNrQgEHA.1392@TK2MSFTNGP11.phx.gbl...
> User clicks submit button only once (even if it click more than once, the
> form will submit only once), so that is not problem.
>
> I have table ORDERS.
> There I have fields:
>
> 1.ORDERID - IDENTITY(1,1) - Primary key
> 2.ORDERDATE (getdate() by default)
> 3.USERID (int)
> 4.STATUS (tinyint)
> 5.WAREHOUSEID(int)
>
> Than I have table ORDERPRODUCTS:
>
> 0.ORDERID(int) - primary Key
> 1.PRODUCTID(varchar(10)) - primary Key
> 2.QUANTITY(int)
> 3.PRICE(decimal)
>
> I don't see other way than to use identity column for order id? I need it
to
> connect products with order.
> What will you do?
>
> Thank you for your answer,
> Simon
>
> "Joe Celko" <jcelko212@earthlink.net> wrote in message
> news:OosFBlHgEHA.2544@TK2MSFTNGP10.phx.gbl...
> > >> Yes, I'm using IDENTITY for my order number ...
> > I'm using everywhere IDENTITY, on all my tables, ... Is something wrong
> > with this? Is there some other way? <<
> >
> > EVERYTHING is wrong with this.  You do not have a relational database, a
> > data model or any data integrity.  You have never read a book on RDBMS
> > or had a course.
> >
> > You are faking a sequential file's positional record number, so you can
> > reference the physical storage location of a row.  You have lost all the
> > advantages of an abstract data model, SQL set oriented programming, you
> > carry extra data and destroy the portability of code!
> >
> > The IDENTITY column is a holdover from the early programming languages
> > which were very close to the hardware.  For example, the fields (not
> > columns; big difference) in a COBOL or FORTRAN program were assumed to
> > be physically located in main storage in the order they were declared in
> > the program.  The languages have constructs using that model -- logical
> > and physical implementations are practically one!  The data has meaning
> > BECAUSE of the program reading it (i.e. the same bits could be a
> > character in one program and be an integer in another)
> >
> > The early SQLs were based on existing file systems.  The data was kept
> > in physically contiguous disk pages, in physically contiguous rows, made
> > up of physically contiguous columns.  In short, just like a deck of
> > punch cards or a magnetic tape.  Most programmer still carry that mental
> > model, which is why I keep doing that rant about file vs. table, row vs.
> > record and column vs. field.
> >
> > But physically contiguous storage is only one way of building a
> > relational database and it is not the best one. The basic idea of a
> > relational database is that user is not supposed to know *how* or
> > *where* things are stored at all, much less write code that depends on
> > the particular physical representation in a particular release of a
> > particular product on particular hardware at a particular time.
> >
> > One of the biggest errors is the IDENTITY column (actually property, not
> > a column at all) in the Sybase/SQL Server family.  People actually
> > program with this "feature" and even use it as the primary key for the
> > table!  Now, let's go into painful details as to why this thing is bad.
> >
> > The first practical consideration is that IDENTITY is proprietary and
> > non-portable, so you know that you will have maintenance problems when
> > you change releases or port your system to other products.  Newbies
> > actually think they will never port code!  Perhaps they only work for
> > companies that are failing and will be gone.  Perhaps their code is such
> > crap nobody else want their application.
> >
> > But let's look at the logical problems.  First try to create a table
> > with two columns and try to make them both IDENTITY.  If you cannot
> > declare more than one column to be of a certain data type, then that
> > thing is not a datatype at all, by definition.  It is a property which
> > belongs to the PHYSICAL table, not the LOGICAL data in the table.
> >
> > Next, create a table with one column and make it an IDENTITY.  Now try
> > to insert, update and delete different numbers from it.  If you cannot
> > insert, update and delete rows from a table, then it is not a table by
> > definition.
> >
> > Finally create a simple table with one IDENTITY and a few other columns.
> > Use a few statements like
> >
> > INSERT INTO Foobar (a, b, c) VALUES ('a1', 'b1', 'c1');
> > INSERT INTO Foobar (a, b, c) VALUES ('a2', 'b2', 'c2');
> > INSERT INTO Foobar (a, b, c) VALUES ('a3', 'b3', 'c3');
> >
> > To put a few rows into the table and notice that the IDENTITY
> > sequentially numbered them in the order they were presented.  If you
> > delete a row, the gap in the sequence is not filled in and the sequence
> > continues from the highest number that has ever been used in that column
> > in that particular table.  This is how we did record numbers in
> > pre-allocated sequential files in the 1950's, by the way. A utility
> > program would then "pack" or "compress" the records that were flagged as
> > deleted or unused to move the empty space to the physical end of the
> > physical file.
> >
> > But now use a statement with a query expression in it, like this:
> >
> > INSERT INTO Foobar (a, b, c)
> > SELECT x, y, z
> >   FROM Floob;
> >
> > Since a query result is a table, and a table is a set which has no
> > ordering, what should the IDENTITY numbers be?  The entire, whole,
> > completed set is presented to Foobar all at once, not a row at a time.
> > There are (n!) ways to number (n) rows, so which one do you pick?  The
> > answer has been to use whatever the *physical* order of the result set
> > happened to be.  That non-relational phrase "physical order" again!
> >
> > But it is actually worse than that.  If the same query is executed
> > again, but with new statistics or after an index has been dropped or
> > added, the new execution plan could bring the result set back in a
> > different physical order.
> >
> > Can you explain from a logical model why the same rows in the second
> > query get different IDENTITY numbers?  In the relational model, they
> > should be treated the same if all the values of all the attributes are
> > identical.
> >
> > Using IDENTITY as a primary key is a sign that there is no data model,
> > only an imitation of a sequential file system.  Since this "magic,
> > all-purpose, one-size-fits-all" pseudo-identifier exists only as a
> > result of the physical state of a particular piece of hardware at a
> > particular time as read by the current release of a particular database
> > product, how do you verify that an entity has such a number in the
> > reality you are modeling?
> >
> > You get a new IDENTITY value everytime you submit the same data, so if
> > your customer hits his mouse tem times, you have ten identical orders. I
> > was guessing that this was your problem.
> >
> > --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!
>
>