Insert a row to some tables with same id

Question

I need to insert an entity to more than one tables. Number of row in tables is not equal but I need to fetch that entity by its id from all the tables. In oracle we can use same sequence for all these tables but what can we do in the mysql to do such thing? (I can’t use foreign key or use last inserted id because each entity insert row in some tables, not all of them and there’s no sequence in inserting into them.) Something like this:

Entities:

E1: a1,a2,a3,a4,a5,a6

E2:b1,b2,b3,b4,b5

E3:c1,c2,c3,c4,c5

E4:d1,d2,d3,d4,d5,d6

Table1:

  Id  | col1 | col2
   5    a1      a2
   3    c1      c2

Table2:

 Id  | col1 | col2
  1     b3     b4
  4     d3     d4
  5     a3     a4

Table3:

  Id  | col1 | col2
   4     b5     b6
   5     a5     a6

Then I can join tables and fetch E1 by id=5

Answer 1

You could use a "secondary key" as InsertID, or something like that, and fetch by that. This way you don't mess up the AI of your primary key (Id).

In case of insert, you could fetch the highest InsertID from all table, and increment that manually for the current insert, so it will be continuous and distinct.

UPDATE: Other way is to use some kind of a relation table. If the number of tables are fix, you can use a fix ID | Table1ID | Table2ID | ... structure, where the [TableName]ID is the foreign key for the tables witch contains the entity.

In case of a variable number of tables, you could use something like this: ID | InsertID | TableID | RowID, where InsertID is the identifier of the insert (this is the id you can query in your case), TableID is the table identifier and RowID is the ID of the entity in a given table.