mysql - Generate Unique AI ID over multiple data tables -

i designing database come struggle @ moment,

currently have 3 tables:

1. ex_artists
2. ex_tracks
3. ex_labels

now wish have unique id throughout these 3(or more) tables

so if 'example artist' had id '1', 'example track' can not have id '1' should id '2' since '1' exists

thank in advance!

i understand concerns. once decide design database technical ids, there danger of confusing ids. while

insert album_track (album, artist, track, no)    values ('b0016991-00', 'jbieber', 'boyfriend0001', 2); 

instead of

insert album_track (album, artist, track, no)    values ('b0016991-00', 'boyfriend0001', 'jbieber', 2); 

would through error,

insert album_track (album_id, artist_id, track_id, no) values (40, 22, 12, 2); 

instead of

insert album_track (album_id, artist_id, track_id, no) values (40, 12, 22, 2); 

would not, , time notice program error may late tell bad records ones. data technically consistent, mess really.

to overcome problem, need 1 source pull ids from. in oracle instance use sequence. in mysql can create id table purpose:

create table ids(id int auto_increment primary key); create table album(id int primary key, album_no text, album_name text,   foreign key (id) references ids(id)); create table track(id int primary key, track_name text, record_date date, take int,    foreign key (id) references ids(id)); insert ids values (); insert album (id, album_no, album_name) values   ((select last_insert_id), 'b0016991-00', 'justin bieber – believe - deluxe edition'); 

so whenever insert record in 1 of tables, must specify id (because not automatically got). id insert ids table , call mysql's last_insert_id().

an less safe, simpler alternative start ids @ different offsets:

create table album(id int auto_increment primary key, album_no text, album_name text); create table track(id int auto_increment primary key, track_name text, record_date date); alter table track auto_increment=10000001; create table artist(id int auto_increment primary key, artist_name varchar(100)); alter table artist auto_increment=20000001; insert artist (artist_name) values ('justin bieber'); 

this works long ids stay in desired range.