Hi all, ive searched the site but couldnt find an answer, so here goes:
a client has a 2 websites displaying products, if they add/update/delete an entry on the 1st site they should have the option to choose whether or not to update the 2nd site, and vica versa.
Each site could have its own mysql db or both sites could share the same database.
What different methods of achieving this are there?
The easiest way to implement this, if scaling is not an issue would be to have a single database, with a table that identifies the possible sites. And then an identifier on all of the necessary records connecting the products to a particular site. On the front-ends you would simply filter based on the SiteId etc. If you make database structure changes all sites receive the changes simultaneously. If you intend to store a lot of records and by a lot of records I mean millions then you will need consider scaling or sharding etc.
The other option is to implement each site in its own identical database, however keeping the structures in-line if the there will be more than two sites can become troublesome. In your application you'd simply need to know the different database server connections and the logic to CRUD a record would be relatively similiar to the first scenario, except it would want to pass the record to each database connection independently.
Having the ability to register database connections as a pool would be critical to pulling off the second scenario, as you would want a single transaction to be pushed to all databases and if one database fails for some reason you would need a scenario to roll back all of the changes. MySQL transactions in InnoDB might be worth looking at for data integrity. All depends on your needs.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.