Phil Latio wrote:

> However I am bit confused as to why I should merge the tables. Someone else
> suggested it too but if I am simply going to add a flag (which switches the
> record on or off), then that should suffice shouldn't it? As I said though,
> if both you and the other chap suggest the same thing, then I am most likely
> missing a trick here somewhere.

Your current situation is something like this:

create table a(
id int unsigned
name text,
age int unsigned,
flag tinyint(1) );

create table b(
id int unsigned
name text,
age int unsigned );

insert into a values(1,'Jack',25);
insert into a values(2,'Jill',24);

Now, to move data from a to b and back from b to a requires these queries:
lock tables a,b;
select * from a where id in(1,2);
// parse data in program and create queries on application side
insert into b values(1,'Jack',25);
insert into b values(2,'Jill',24);
update a set flag=1 where id in(1,2);
unlock tables;

lock tables a,b;
update a set flag=0 where id in(1,2);
delete from b where id in(1,2);
unlock tables;

####
Now, assume that you would merge these tables and add single flag:
create table a(
id int unsigned
name text,
age int unsigned,
flag tinyint(1) );

insert into a values(1,'Jack',25,0);
insert into a values(2,'Jill',24,0);

Now, to move data "from a to b and back"
update a set flag=1 where id in(1,2);
update a set flag=0 where id in(1,2);


Now, what advantages does this have:
- it was faster to write the example
- it has less queries -> less code and complexity to your program
- it is faster to move data
- requires less power from cpu (server and client)
- requires less memory from the server and the client
- requires less hard drive space from the server
- is less vulnerable to programming errors (someone might easily forget
for example to lock tables which might cause problems),
- is less vulnerable to system errors (less calls to database, less
action with hard drive -> less errors)
- doesn't require client to lock tables (=faster in multi-user systems),
- the database structure is more simple which means less learning time
for someone new to the project.
- Faster to check whether item is in either of the tables.

Disadvantages:
- Query to search items in only in a or b is slightly slower, but with
an index on the flag-column, this shouldn't be an issue.

Do you now understand better, or do you think I missed some advantages
or disadvantages?