Jump to content

Archived

This topic is now archived and is closed to further replies.

a2bardeals

id auto_increment on multiple tables

Recommended Posts

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 [b]rather than[/b] 00001 becuase its the first in the table 'duplication'. This is for order processing and shipping sanity.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Yes, you should have an orders table, and use the UIDs auto-generated from that table as FK to transfers & duplications.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.