After some research on the topic, I am looking towards merging multiple table sets (one per user) to a single set of tables with an extra field in each table for identifying the user a particular record belongs to.
I’d prefer to make the abstraction layer on the database side to keep the changes in the application minimal. For that purpose, I am going to use triggers and create a set of views for each user.
My questions are the following:
I don’t know how MySQL handles views, so is there an extra overhead in database having a lot (millions) of views?
As I understand, all indexes should be prepended by user_id field, so instead of:
create table username_posts ( id integer not null auto_increment , title varchar(32) , contents text , primary key (id) , key (title) );
I would have to use:
create table global_posts ( id integer not null auto_increment , title varchar(32) , contents text , user_id integer not null , primary key (user_id, id) , foreign key (user_id) references users(id) , key (user_id, title) );
But I get “Error in table definition”, as I am using InnoDB and it requires auto_increment column to be first in the list. Is there any way around it?
- Are there any other issues I should take into account when doing the migration?