Multiple Tables Sharing Autoincrement Index?

Wynder

It it possible to have, for example, four different tables share the same autoincrement index?

devinemke

assuming that all 4 tables are always INSERTed simultaneously then do the insert on one table, use [man]mysql_insert_id[/man] (assuming you are using mySQL) to grab that last insert then use then id for the subsequent inserts.

Wynder

assuming that all 4 tables are always INSERTed simultaneously then do the insert on one table, use mysql_insert_id (assuming you are using mySQL) to grab that last insert then use then id for the subsequent inserts.

Actually, the table that's inserted into is dependant upon the location of the person who is inserting a record. I wanted to use the ID as something like a 'Ticket Number', but for four different locations. I'm looking to make the numbers sequential regardless of location instead of having a #50 for all locations.

id10t

Let the autoincrement do its thing, especially if you are using it as a primary key. To solve your "problem" - have a second function that assigns a ticket number in a separate table with all the other information about it (site location, etc).

Wynder

I considered that as an option initially, but the reason for splitting it up into 4 different tables was for four different locations -- mainly to keep the searches fast and data organized because it's going to grow very, very quickly...

If I did that, I would only need a single table and if I did that, I wouldn't need the table to track location and index because it could all be done in that one table.

Maybe using a single table will be the way to go in this respect...

Sxooter

PostgreSQL can do this. With MySQL you might want to make a "dummy" table with nothing but the autoinc field, and use that to generate the sequence...

AstroTeg

A single table sounds like a good fit. All like data together in one table is a good design practice (and if indexed properly, performance won't be a problem).

Otherwise, I think Sxooter's approach is about the best you can do with MySQL and multiple tables.

Sxooter

From a relational perspective, the answer is 1 table 99% of the time, I concur on that. I'd redesign as much as I could to get it down to that one table too. then use a catagory table to differentiate the entries. the advantage, of course, is that adding categories requires no new tables, just a new row in the category table.