I am building an application in PHP/MySQL. It contains 2 databases. Database 1 is used to temporarily store orders from the end user. After the user has confirmed the order, it will be copied to database 2 into 2 tables with a SELECT...INSERT. Then the tables in database 1 will be cleaned so that the user can procees with a new order. In other words, the tables in database 1 will never hold more than 1 order at any given time. The two tables hold the order and its corresponding items respectively. The table structures are identical in both database 1 and database 2. When the items belonging to an order are transfered to the table in database 2, there will be a key conflict.
Question 1: What is the best way to solve this key conflict?
I tried to select the columns explicitly except for the key, but this does not solve the problem entirely. It would be nice to use an INSERT INTO db2.table (SELECT * FROM db1.table) query to transfer this data instead of having to mention all columns explicitly.
Also, when the user wants to edit the current record right after it has been dumped into database 2, it is not possible because the tables in database 1 and database 2 cannot be linked. Besides, the record can be INSERTed twice into database 2.
Question 2: How can the user still edit the current record without having to exit the menu and look up the record in the tables of database 2?
Question 3: How can I avoid INSERTing the same record twice into the tables in database 2?
This design with 2 databases was chosen because of the modular approach of the project. Besides, database 2 is separated from database 1 meaning that they cannot interfere. And, since the database is split in 2 parts, it is easier to program two small chunks rather than 1 big chunk.
Having 2 databases is not a bad idea.
I would approach this with the "foreign key" relationship between 2 tables concept. When you insert all the contents from the db1 record into db2, add a new "foreign key" field to db2 (db2_fkey) containing the "primary key" from the db1 record.
This way you can read records on db2 and relate back to the record on db1 using db2_fkey. You will be able to determine that the record has been cleared from db1. Also you will be able to have more than 1 record on db2 with the same value in the db2_fkey field. There's automatic processing in MySQL with a foreign key between 2 tables that you will have to code manually with this approach (like deleting records from both tables). But this relationship approach should answer your application questions.