| Merging multiple tables [message #1065] |
Wed, 11 April 2007 05:18 |
 Kynlem
Messages: 11
Registered: February 2007
Location: Ukraine |
Junior Member |
|
|
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?
|
|
|
Members
Search
Help
Register
Login
Home
