I disagree. My primary keys (mySQL) are ALWAYS not null, autoincrement
integers. It guarantees me a unique key, until it rolls over.
On Friday, February 4, 2005, at 12:05 PM, Rick Sanders wrote:
Auto Incementing numbers are good, but don't work if you want to do a
relational query between 2 tables (Customers & Orders) and if you
delete an
item from the database, it re-numbers all the records.
I don't think so. What flavor database you using?
I'm talking about the default settings on MS SQL server, MYSQL, and
Access. As far as I know, Pervasive SQL & Oracle don't re-number the
records.
Regarding relational queries, you can't do a relational query between
2 database tables that have the primary key as an auto-incrementing
number, unless you really modify the queries and know what you're
doing. I did this successfully a year ago, but found it was more
hassle than just keeping a numeriv field and assigning unique numbers
by using the application, which is standard for windows programs and
accounting packages.
Rick Sanders
Also, if you want to input an order with several items into the same
table,
you want the order number to be the same for all the items, unless
you make
2 tables, one for orders & one for line items.
I'd expect that. In fact, orders, ship_orders, and order_items
So that if an order gets broken into shipping batches, you can keep
tabs on
each batch. But that all depends on what you're selling, I guess.
Rick Sanders
Another way to do the same thing is to have the UID in the table an
auto-incrementing integer.
When an order is created, you enter the new record, retrieve the
newly
created UID, and there you are.
On 2/4/05 10:28 AM, "Rick Sanders" <[EMAIL PROTECTED]> wrote:
Hi Steve,
I've used this code, and it's been very rock-solid on creating
unique
numbers:
1. See if there's any numbers yet in the database, if not assign
the
first
number, in this case it's 1.
<@IF EXPR="<@NUMROWS array=resultset><1">
<@ASSIGN NAME="ordernumber" value="1" scope=user>
<@ELSE>
2. Sort the resultset in numerical order.
<@SORT ARRAY="resultset" SCOPE="local" COLS="1 NUM">
3. Assign the new number, by adding 1 to the largest number
retrieved in
the
array.
<@ASSIGN NAME="ordernumber" value="<@CALC
EXPR='@@local$resultset[<@NUMROWS
array=resultset>,1]+1'>" scope=user>
</@IF>
Rick Sanders
I use the following to generate an invoice number for purchases:
<@assign user$OrderNumber "<@currentdate
format=datetime:%Y%m%d><@tstosecs
<@currenttimestamp>>">
I want these numbers to be unique. I should have thought of this
before,
because I am going to have a problem if 2 orders are entered
within the
same
second. Right now I have an 18 digit invoice number, but I would
like to
have it 15 digits or less and I am using 8 for the date.
Anyone else using a different strategy related to time, taking
into
account
the parts of a second, that is fifteen digits or less.?
Thanks
Steve Fogelson
Internet Commerce Solutions
__________________________________________________________________
______
TO UNSUBSCRIBE: Go to
http://www.witango.com/developer/maillist.taf
___________________________________________________________________
_____
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
-----------------------------------------
Roland Dumas
Roberts Information Services
310 W. Bellevue Avenue
San Mateo CA 94402
650-347-1373
415-412-9300 (cell)
[EMAIL PROTECTED]
SMS: http://new.servqual.com/html/sms.tml
____________________________________________________________________
____
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
_____________________________________________________________________
___
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
-----------------------------------------
Roland Dumas
Roberts Information Services
310 W. Bellevue Avenue
San Mateo CA 94402
650-347-1373
415-412-9300 (cell)
[EMAIL PROTECTED]
SMS: http://new.servqual.com/html/sms.tml
______________________________________________________________________
__
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
_______________________________________________________________________
_
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
