Hi, Yes this is correct if you are talking relations between 2 DATABASES we have all encountered data sync'ing and publishing issues.
The question is about using 2 TABLES or 1 with an auto increment The way that I like to do it, and this doesn't supersede or in any way denigrate the other suggestions on the list. I use a small 1 column 1 row table (for example PONumber)in conjunction with a stored procedure. To get the value you must go through the stored procedure. Stored Procedure psudo code ============================= Set lock on PONumber Get Number Increment number Update PONumber to new incremented number Set lock off Return new number. Pros ----- 1. You get a nice simple number 2. You can control the format,sequence,... of nice simple number 3. You don't have to worry about autonumber accidently get set to renumber Just my humble open onion ;-) Ben Johansen - http://www.pcforge.com Authorized Witango & MDaemon Reseller Available for Web App. Developement -----Original Message----- From: Rick Sanders [mailto:[EMAIL PROTECTED] Sent: Friday, February 04, 2005 2:02 PM To: [email protected] Subject: Re: Witango-Talk: Unique Purchase number Taken from the MYSQL forum: I have two different databases they both have an auto-increment PK field and while they are different databases with different names, they do have tables with the same names. What I find is that the auto-crement integer number remembers what it is across these databases. So if I insert in the 1st DB and the number is 24, then I insert in the 2nd DB the number will be 25. I would of thought that the number would be "fresh" and independent between the DBs. Could you explain? Here's the link:http://lists.mysql.com/mysql/178855Rick Sanders > > > > On 2/4/05 12:05 PM, "Rick Sanders" <[EMAIL PROTECTED]> 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. > > Default on mysql is to leave those numbers alone. You delete one and you > have a gap in the sequence. The databases may reuse the location in the > database so that if you just list records, they will appear out of > sequence > (if you have records 1-1000 and delete # 3, then record #1001 will appear > right after #2 in an unsorted list.), but I think the norm for all > databases > is to leave the auto-incremented unique numbers alone. They lose their > value > if they jitter around. > > >> >> 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. > > For each table, you create a UID field, which is the auto-incrementing > unique integer. > > For child tables, you have a field, which is the parent_UID so that you > can > make the many:one relation. > > Then you make the join where child.parent_uid = parent.uid > > >> 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. > > > It's no more difficult than any other unique number. When you create the > new > record and retrieve the new UID, you've got the order number. You can send > that to the customer, put it in a log, have it display to fulfillment or > customer service. You can fetch the record with one argument. It has some > extra benefits of making it very easy to see the order sequence, i.e., > order > number 234 came right before 235. So it's not only unique, but provides > some > other information about the record. > > I'm thinking you're using terms differently than we are or not quite > comfortable with setting up joins. You CAN create UIDs by other means, but > the database gives you a readymade mechanism to do this, so why not take > the > easy way out? > > > > > ________________________________________________________________________ > 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
