Jump to content


Photo

id auto_increment on multiple tables


  • Please log in to reply
4 replies to this topic

#1 a2bardeals

a2bardeals
  • Members
  • PipPipPip
  • Advanced Member
  • 62 posts
  • LocationAnn Arbor, MI

Posted 16 October 2006 - 11:51 PM

i have an online order process witch places orders into two types of orders reperesented by two different tables in a MySQL database ('transfers' & 'dupliaction'). I am trying to create a unique order number ('ID') field in both tables that auto_increments but so that there are not duplicate ids in the 2 tables.
Basically,
If someone places a transfer order it assigns the order ID: 00001 in the table 'transfers' and then another customer comes along and places a duplication order it will assign the duplication order ID: 00002 rather than 00001 becuase its the first in the table 'duplication'. This is for order processing and shipping sanity.

#2 Ninjakreborn

Ninjakreborn
  • Members
  • PipPipPip
  • Information Technology Specialist
  • 3,922 posts
  • Age:33

Posted 17 October 2006 - 01:47 PM

You would have to have a go-between table, that records what id's have been used, and what has not, so you can assign id's according to that.  Rather complicated process.  I don't see the point, there probably are other solutions, other than the way you are trying to do it, what are you trying to do exactly overall.

------

Business Website: http://www.infotechnologist.biz

Personal Website: http://www.joyelpuryear.com

Blog Site: http://www.realmofwriting.com
Services: Web development, application development, mobile development, and custom development. All services listed on my website.


#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 17 October 2006 - 03:53 PM

Yes, you should have an orders table, and use the UIDs auto-generated from that table as FK to transfers & duplications.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 a2bardeals

a2bardeals
  • Members
  • PipPipPip
  • Advanced Member
  • 62 posts
  • LocationAnn Arbor, MI

Posted 18 October 2006 - 12:13 AM

well the orders for transfers have totally different options (fields) than the duplication orders so its hard to have them in the same table. For web sanity i need to keep them seperate and for accounting and customer service sanity i need to have a unique id for each different project. I will try the go between table and let you know how it works.

p.s. it would be nice if somehow i could add an identifier in the Order Number like WT0003 for a "Web: Transfer" or WD0004 for "Web: Duplication".

Can i generate the number from MySQL auto_increment or would i have to write a script that checks the last four digits of the last order in the orders table and adds one?

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 19 October 2006 - 04:06 PM

I didn't say to put them in the same table, just to use the same FK for each set of related records.  And no, you can't auto-increment on anything other than a pure integer field.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users