No announcement yet.

Merging multiple tables

  • Filter
  • Time
  • Show
Clear All
new posts

  • Merging multiple tables

    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:

    1) I don't know how MySQL handles views, so is there an extra overhead in database having a lot (millions) of views?

    2) 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?

    4) Are there any other issues I should take into account when doing the migration?