Re: Doubling the order

From: simon (simon.zupan_at_stud-moderna.si)
Date: 08/13/04 

Date: Fri, 13 Aug 2004 09:52:47 +0200

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!