用于库存管理系统的 SQL 与 NoSQL [英] SQL vs NoSQL for an inventory management system

问题描述

我正在开发一个基于 JAVA 的 Web 应用程序.主要目的是为在多个称为渠道的网站上销售的产品提供库存.我们将担任所有这些渠道的经理.我们需要的是:

1. 用于管理每个渠道的库存更新的队列.
2. 具有每个渠道上正确分配快照的库存表.
3. 将会话 ID 和其他快速访问数据保存在缓存中.
4. 提供类似 Facebook 的仪表板 (XMPP) 以让卖家尽快更新.

我正在研究的解决方案是 postgres(我们的数据库到目前为止处于同步复制模式)、NoSQL 解决方案，如 Cassandra、Redis、CouchDB 和 MongoDB.

我的限制是:

1. 库存更新不会丢失.
2. 作业队列应按顺序执行，最好不要丢失.
3. 易于/快速的开发和未来的维护.

我愿意接受任何建议.提前致谢.

解决方案

1. 用于管理每个渠道的库存更新的队列.

这不一定是数据库问题.您可能最好查看消息系统(例如 RabbitMQ)

<块引用>

1. 具有每个渠道上正确分配快照的库存表.
2. 将会话 ID 和其他快速访问数据保存在缓存中.

会话数据可能应该放在更适合任务的单独数据库中(例如 memcached、redis 等)没有万能的数据库

<块引用>

1. 提供类似 Facebook 的仪表板 (XMPP) 以让卖家尽快更新.

我的限制是:1. 库存更新不能丢失.

有3种方法可以回答这个问题:

1. 此功能必须由您的应用程序提供.数据库可以保证拒绝和回滚坏记录，但不能保证每个查询都会被输入.该应用必须足够智能，才能识别何时发生错误并重试.

2. 有些 DB 将记录存储在内存中，然后定期将内存刷新到磁盘，这可能会在断电的情况下导致数据丢失.(例如，除非您启用日记功能，否则 Mongo 默认以这种方式工作.CouchDB 始终附加到记录(即使删除也是附加到记录的标志，因此数据丢失非常困难))

3. 有些数据库的设计非常可靠，即使发生地震、飓风或其他自然灾害，它们也能保持耐用.其中包括 Cassandra、Hbase、Riak、Hadoop 等

您指的是哪种类型的耐用性?

<块引用>

1. 作业队列应按顺序执行，最好不要丢失.

大多数 noSQL 解决方案更喜欢并行运行.所以你有两个选择.1.使用为每个查询锁定整个表的数据库(较慢)2. 将您的应用程序构建为更智能或事件(客户端顺序队列)

<块引用>

1. 易于/快速的开发和未来的维护.

通常，您会发现 SQL 一开始开发速度更快，但更改可能更难实现noSQL 可能需要更多计划，但更容易进行临时查询或架构更改.

您可能需要问自己的问题更像是:

1. 我是否需要进行更适合 Map/Reduce 的密集查询或深入分析?"

2. 我需要经常更改架构吗?

3. 我的数据是否高度相关?以什么方式?"

4. 我选择的数据库背后的供应商是否有足够的经验在我需要时为我提供帮助?"

5. 我需要地理空间索引、全文搜索等特殊功能吗?"

6. 我需要我的数据的实时性有多接近?如果直到 1 秒后我的查询中才看到最新记录，会不会很痛苦?什么水平的延迟是可以接受的?"

7. 在故障转移方面我真正需要什么"

8. 我的数据有多大?它适合内存吗?它适合一台计算机吗?每个单独的记录是大还是小?

9. 我的数据多久会更改一次?这是存档吗?"

如果您要拥有多个客户(渠道?)，每个客户都有自己的库存模式，那么基于文档的数据库可能具有它的优势.我记得有一次我查看了一个有库存的电子商务系统，它有近 235 张桌子！话又说回来，如果你有一定的关系数据，SQL 解决方案也确实有一些优势.

我当然可以看到如何在给定的约束条件下使用 mongo、couch、riak 或 orientdb 构建解决方案.但至于哪个最好?我会尝试直接与数据库供应商交谈，也许会观看 nosql 磁带

I am developing a JAVA based web application. The primary aim is to have inventory for products being sold on multiple websites called channels. We will act as manager for all these channels. What we need is:

1. Queues to manage inventory updates for each channel.
2. Inventory table which has a correct snapshot of allocation on each channel.
3. Keeping Session Ids and other fast access data in a cache.
4. Providing a facebook like dashboard(XMPP) to keep the seller updated asap.

The solutions i am looking at are postgres(our db till now in a synchronous replication mode), NoSQL solutions like Cassandra, Redis, CouchDB and MongoDB.

My constraints are:

1. Inventory updates cannot be lost.
2. Job Queues should be executed in order and preferably never lost.
3. Easy/Fast development and future maintenance.

I am open to any suggestions. thanks in advance.

解决方案

1. Queues to manage inventory updates for each channel.

This is not necessarily a database issue. You might be better off looking at a messaging system(e.g. RabbitMQ)

1. Inventory table which has a correct snapshot of allocation on each channel.
2. Keeping Session Ids and other fast access data in a cache.

session data should probably be put in a separate database more suitable for the task(e.g. memcached, redis, etc) There is no one-size-fits-all DB

1. Providing a facebook like dashboard(XMPP) to keep the seller updated asap.

My constraints are: 1. Inventory updates cannot be lost.

There are 3 ways to answer this question:

1. This feature must be provided by your application. The database can guarantee that a bad record is rejected and rolled back, but not guarantee that every query will get entered. The app will have to be smart enough to recognize when an error happens and try again.

2. some DBs store records in memory and then flush memory to disk peridocally, this could lead to data loss in the case of a power failure. (e.g Mongo works this way by default unless you enable journaling. CouchDB always appends to the records(even a delete is a flag appended to the record so data loss is extremely difficult))

3. Some DBs are designed to be extremely reliable, even if an earthquake, hurricane or other natural disaster strikes, they remain durable. these include Cassandra, Hbase, Riak, Hadoop, etc

Which type of durability are your referring to?

1. Job Queues should be executed in order and preferably never lost.

Most noSQL solutions prefer to run in parallel. so you have two options here. 1. use a DB that locks the entire table for every query(slower) 2. build your app to be smarter or evented(client side sequential queuing)

1. Easy/Fast development and future maintenance.

generally, you will find that SQL is faster to develop at first, but changes can be harder to implement noSQL may require a little more planning, but is easier to do ad hoc queries or schema changes.

The questions you probably need to ask yourself are more like:

1. "Will I need to have intense queries or deep analysis that a Map/Reduce is better suited to?"

2. "will I need to my change my schema frequently?

3. "is my data highly relational? in what way?"

4. "does the vendor behind my chosen DB have enough experience to help me when I need it?"

5. "will I need special feature such as GeoSpatial indexing, full text search, etc?"

6. "how close to realtime will I need my data? will it hurt if I don't see the latest records show up in my queries until 1sec later? what level of latency is acceptable?"

7. "what do I really need in terms of fail-over"

8. "how big is my data? will it fit in memory? will it fit on one computer? is each individual record large or small?

9. "how often will my data change? is this an archive?"

If you are going to have multiple customers(channels?) each with their own inventory schemas, a document based DB might have it's advantages. I remember one time I looked at an ecommerce system with inventory and it had almost 235 tables! Then again, if you have certain relational data, a SQL solution can really have some advantages too.

I can certainly see how I could build a solution using mongo, couch, riak or orientdb with the given constraints. But as for which is the best? I would try talking directly DB vendors, and maybe watch the nosql tapes

这篇关于用于库存管理系统的 SQL 与 NoSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！