On 02/04/2016 03:15 PM, Derek Caswell wrote: > 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. >
With your particular set of requirements I would set up an entirely new separate database and implement #1 on it. Because while you already have duplicated data across these system you do not want to create more duplicated data that you need to synchronize. I know this sounds like it will hurt query efficiency, but I don't think it will do so as much as you might think. Option 2 is bad because you have an unknown number of databases, which means you don't know how many fields you need to add to each table. With option #1 you can create four tables and easily add as many databases and tables as you need. You would do this by creating: Table 1 databases database id, database name(ip, company, etc if desired) Table 2 tables table id, databasae id, table name(schema?),primary id Table 3 keys key id, key name,(info about key) Table 4 key matching key id, table id Then you can run a query grabbing all the tables with match key ids and sync them. You may want a fifth table with table ids and column names to sync. Of course that assumes they have matching names. You may need to add more tables to match columns since the column names are unlikely to match across databases. Kyle _______________________________________________ UPHPU mailing list [email protected] http://uphpu.org/mailman/listinfo/uphpu IRC: #uphpu on irc.freenode.net
