如何保持两个文章表同步，但保持股票分离 [英] How do I keep two article tables synced, but keep stock separate

问题描述

这可能是一个更概念性的问题，但我找不到一个简单的解决方案。

场景：两个商店（说'M'和'S' 。M是主数据库并确定数据库中的文章，每个维护一个独立的库存我有M的文章表复制到S，我把库存分成一个单独的表与一个共同的参考。

现在，当新文章添加到M中时，它们也到达S，但是它们在S的股票表中没有条目类似的删除文章的问题可能的解决方案：

• 每当有新的（未测试存在的）文章发出
  请求时，我在S的股票表中创建一个条目？ / p>




• 我必须定期扫描以检查是否缺少股票项目。

有没有更优雅的方式来解决这个问题？

注意：为了澄清，让我解释另一种方式：

M已经将'articles'表复制到S（使用MySQL的复制机制）。
这很好。

问题是M和S有stock表，它们是每个M和S的局部。 ，例如，将新产品添加（M）到文章表中，并且转移到S.现在有新的条目，在S的股票表中没有相应的条目。

我猜这不是一个不常见的情况 - 解决这个问题的正常程序是什么？

解决方案

除非您有两个不同的数据库服务器上的数据库，为什么不创建一个表 articles 和引用它的表 stock 。您可以添加商店（最好是 shop_id ）作为后者的额外列。类似的东西：

  CREATE TABLE articles（id int primary key not null，
 name char（20）not null ）; 
 
 CREATE TABLE stock（article_id int not null，
 shop ENUM（'M'，'S'）not null，
 qty int not null，
 FOREIGN KEY （article_id）REFERENCES articles（id），
 UNIQUE（article_id，shop））; 
  

请参阅 http://sqlfiddle.com/#!2/e6eca/5 。

如果你真的需要限制在表 articles 到商店M 上创建可以通过为您的数据库创建不同用户（* user_m *，* user_s *）并使用 REVOKE 和/或 GRANT 设置各种访问权限。

---

EDIT：如果这两个服务器位于远端站点，您可能可以使用 MySQL复制功能，以保持一个或多个表在两个网站之间同步。这将需要两个站点之间的网络访问。就我自己而言，出于显而易见的原因，我会考虑在两个地点之间使用安全隧道。最后，您可能还需要在数据库级别设置权限，以便只允许从一个网站插入，而不允许从其他网站插入。

作为一个穷人解决方案，您最终可以定期备份所需的表，从一个服务器更新第二个服务器上的表。 mysqldump + cronjob的组合将是很好的起点。

自己，我会推送到 MySQL复制。设置可能更复杂。但是，这将表现更好，规模更好，延迟更低。

This is probably a more conceptual problem, but I couldn't find an easy solution.

Scenario: Two shops (say 'M' and 'S']. M is the master and determines the articles in the databases. Each maintains an independent stock. I have M's article table replicating to S, and I separated stock into a separate table with a common reference.

Now when new articles are added in M, they arrive at S too, but they won't have an entry in S's stock table. Similar problem with delete articles. Possible solutions:

• Do I create an entry in S's stock table each time a request is made for a new (not-test-existing) article?

• Do I have to scan regularly to check for missing stock entries.

Isn't there a more elegant way to solve this?

NOTE: To clarify, let me explain another way:

M already replicates the 'articles' table to S (using MySQL's replication mechanism. This works fine.

The problem is that M and S have 'stock' tables which are local to each M and S. What is the normal procedure when, for example, a new product is added (in M) to the 'articles' table, and transferred to S. Now there is new entry which doesn't have a corresponding entry in S's stock table.

I'm guessing this is not an unusual situation - what would be the normal procedure to solve this?

解决方案

Unless of course if you have two databases located on two different DB servers, why don't you simply create a table articles and a table stock referencing it. You could add the shop (ideally the shop_id) as an extra column of that latter. Something like that:

CREATE TABLE articles(id int primary key not null,
                      name char(20) not null);

CREATE TABLE stock(article_id int not null,
                   shop ENUM('M', 'S') not null,
                   qty int not null,
                  FOREIGN KEY(article_id) REFERENCES articles(id),
                  UNIQUE(article_id, shop));

Please see http://sqlfiddle.com/#!2/e6eca/5 for a live example.

If you really need to restrict creation of items on table articles to shop M that could be achieved by creating different users for your DB (*user_m*, *user_s*) and using REVOKE and/or GRANT to setup the various access rights.

---

EDIT: If the two servers are on distant sites, you would probably be able to use MySQL replication capabilities to keep one or many tables in sync between the two sites. This will require a network access between the two sites. As of myself, for obvious reasons, I would consider using a secure tunnel between the two sites. Finally you still probably have to set-up permissions at DB-level to only allow insert from one site and not the other.

As a "poor's man" solution, you finally have the possibility to backup on regular basis the required tables from one server to update the tables on the second server. A combination of mysqldump + cronjob would be good starting point.

As of myself, I would though push to MySQL replication. The setup is probably more complex. But this will perform better, scale better and have lower latency.

这篇关于如何保持两个文章表同步，但保持股票分离的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！