Thanks for the ideas. @Tod That isn't quite what I am looking for but it helps me to know where I need to further clarify. The reason that we have the same schema in multiple databases isn't for backup or distributing load in the traditional sense. Our system is simply setup to have each customer with their own database. We are starting to implement functionality, however, that shares some data between the customers/databases. Because of the way our system is setup, we need to have that shared data on each database but because the databases work independently, their keys, etc. will be different. We do have a number that ties the 2 reservations together but that is only on one table so it doesn't help with the other tables that need to be synchronized.
@Merrill I wouldn't be able to edit the way the primary keys are being generated. What you described for creating many to many relationships was basically what my first option was doing with the associative table. The concatenation idea is an interesting idea for the foreign key in my second idea but I think that would get more complicated then the associative table. I also should clarify that this system has been running for many years with a lot of existing data so I can't make any changes to the database that would not be backwards compatible. Derek On Thu, Feb 4, 2016 at 3:48 PM Tod Hansmann <[email protected]> wrote: > You may want a master-master replication scheme with primary key offsets. > This means DB 1 can do writes to any table and the auto id increment is > something like 10, and it's offset is 1. So DB 1's primary key assignment > sequence goes something like 1, 11, 21, 31, etc. DB 2 has an offset of 2 > and keys get assigned as 2, 12, 22, 32, etc. Then the logs replay in a > round-robin scheme like that so everyone gets the appropriate updates (you > just can't write the same thing to two databases if you have a unique > constraint). > > You do have to be clever in how you heal errors. For instance, logs play > through on DB1 get replayed on DB2 and then get pushed back to DB1, so you > have to set DB1 to ignore updates from itself, and then if DB3 goes down, > you have to point DB2 -> DB4 or everything up the chain doesn't get > updates, and replay log files start reaching maximum capacity. > > The advantage to this strategy means you can point whatever consumer of > the DB to any of the DB nodes for reading or writing and treat it like it > is the entire database. The disadvantage is you can't shard anything out, > but (no offense to MySQL, it's great for what it is) since you're using > MySQL I'm guessing your DB is handling a lot of requests, as opposed to not > fitting within a few terabytes. This is not the solution for you if you're > much bigger than that. > > > -Tod Hansmann > Problem Solver > www.phonejanitor.com > 801-618-0059 > > On Thu, Feb 4, 2016 at 3:15 PM, Derek Caswell <[email protected]> > 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. >> 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 >> > > _______________________________________________ UPHPU mailing list [email protected] http://uphpu.org/mailman/listinfo/uphpu IRC: #uphpu on irc.freenode.net
