I am having an issue right now where I have multiple databases that have the same structure. They have tables for storing a reservation such as res, res_passengers, res_bags, etc. In certain circumstances these reservation tables will be duplicated across the databases. I am creating a script that synchronizes the data from one of the databases to the others. The problem is that the primary ID's are not going to be the same so there isn't currently a way in the structure to say that passenger 1 on the first database is the same as passenger 3 on the second database. We are trying to figure out how to change the structure so that we can tie those together. We have thought of a couple options:
1. Create an associative table that ties them together but because the number of passengers is unknown, the number of databases is unknown, and there are about 15 tables we are going to have to synchronize, that seemed to get really complicated really quickly. 2. Another idea we had was to add a field to each table that acts as a foreign key. For example, passenger 1 on database 1 would have a field that contains 12 and passenger 3 on database 2 would also have 12 in that field. So all passengers would have the same number in that field. The only issue with that is we don't have a table that contains a primary key that can be used as the foreign key. We don't really want to create a table that just has a primary key. We are leaning towards this idea because it would make our queries simpler to write and more efficient but we aren't sure how to manage those foreign key ID's if we don't have a table to keep track of them. We are using MySQL and we are trying to take into account scalability and query efficiency as well. I am writing to you guys to see what approach you would take on this. We are open to any ideas you might have. Thanks for any help you might be able to give. Derek _______________________________________________ UPHPU mailing list [email protected] http://uphpu.org/mailman/listinfo/uphpu IRC: #uphpu on irc.freenode.net
