SQL VS的NoSQL数据，这将是psented到用户$ P $已添加多个过滤器后， [英] SQL vs NoSQL for data that will be presented to a user after multiple filters have been added

问题描述

我即将踏上工作的一个项目，这是非常超出了我正常的职责范围。作为一个SQL DBA，我最初的倾向是接近使用SQL数据库，但我了解NoSQL的越多，我越认为，这可能是更好的选择项目。我希望我可以用这个问题在较高的水平描述项目获得使用每个选项的利弊一些反馈。

I am about to embark on a project for work that is very outside my normal scope of duties. As a SQL DBA, my initial inclination was to approach the project using a SQL database but the more I learn about NoSQL, the more I believe that it might be the better option. I was hoping that I could use this question to describe the project at a high level to get some feedback on the pros and cons of using each option.

该项目是相对比较简单。我有一组具有不同的属性的对象。其中一些属性是共同的所有对象而一些是共同的唯一的对象的子集。我所负责与建筑物是在用户选择了一系列基于一个对象的属性，然后返回匹配所有的过滤器^对象的列表的过滤器的服务。当用户选择一个过滤器，他或她可过滤在共同的或子集的属性，但是抽象的前端。

The project is relatively straightforward. I have a set of objects that have various attributes. Some of these attributes are common to all objects whereas some are common only to a subset of the objects. What I am tasked with building is a service where the user chooses a series of filters that are based on the attributes of an object and then is returned a list of objects that matches all^ of the filters. When the user selects a filter, he or she may be filtering on a common or subset attribute but that is abstracted on the front end.

<青霉> ^有机会的，这取决于用户的反馈，该对象列表可仅匹配一些过滤器和匹配的质量将通过一个得分被显示给用户，指示多少的标准相匹配。的

马丁Folwler看这次谈话之后（ http://www.youtube.com/watch?v=qI_g07C_Q5I），这似乎是一个文档样式的NoSQL数据库应适合我的需要，但因为我有这种方法没有任何经验，也有可能是我失去了一些东西明显。

After watching this talk by Martin Folwler (http://www.youtube.com/watch?v=qI_g07C_Q5I), it would seem that a document-style NoSQL database should suit my needs but given that I have no experience with this approach, it is also possible that I am missing something obvious.

一些额外的信息 - 该数据库将最初具有与含有10至50的属性的每个对象，但对象数约5000的对象将随着时间的推移绝对生长和属性的数目可能增长，这取决于用户的反馈。此外，我希望能有对产品所做的快速变化，因为我得到用户的反馈，这样的灵活性是非常重要的能力。

Some additional information - The database will initially have about 5,000 objects with each object containing 10 to 50 attributes but the number of objects will definitely grow over time and the number of attributes could grow depending on user feedback. In addition, I am hoping to have the ability to make rapid changes to the product as I get user feedback so flexibility is very important.

任何意见将非常AP preciated，我会很乐意提供更多的信息，如果我已经离开了东西我的讨论至关重要。谢谢。

Any feedback would be very much appreciated and I would be happy to provide more information if I have left anything critical out of my discussion. Thanks.

推荐答案

不妨让这个答案。我要评论说，我不强的NoSQL，所以我倾向于对SQL倾斜。

May as well make this an answer. I should comment that I'm not strong in NoSQL, so I tend to lean towards SQL.

我会做这个作为三表集。你会看到它被称为实体值对逻辑在网络上......这是处理多个动态属性物品的方式。比方说你有一堆的产品，每个人都有的一些属性。

I'd do this as a three table set. You will see it referred to as entity value pair logic on the web...it's a way of handling multiple dynamic attributes for items. Lets say you have a bunch of products and each one has a few attributes.

Prd 1 - a,b,c
Prd 2 - a,d,e,f
Prd 3 - a,b,d,g
Prd 4 - a,c,d,e,f

因此​​，这里有4种产品和6个属性...同样的理论将数百个属性的产品和数以千计的工作。的一个表中保持上述标准方法需要具有6列存储数据（在此设置它们中的至少三分之一都为空）沿着产品信息。新属性添加的手段改变表中另一列添加到它，并拿出一个脚本来填充现有的或刚离开它空了所有现有的。这不是最好玩，可以是头部疼痛。

So here are 4 products and 6 attributes...same theory will work for hundreds of products and thousands of attributes. Standard way of holding this in one table requires the product info along with 6 columns to store the data (in this setup at least one third of them are null). New attribute added means altering the table to add another column to it and coming up with a script to populate existing or just leaving it null for all existing. Not the most fun, can be a head ache.

解决这个问题的替代方案是一个名字值对设置。你想要一个'头'表来保存amoungst你的产品的共同价值观（如姓名，或价格，这样的东西都rpoducts总是有）。在上面的例子中，你会发现，属性A正在对每个记录使用......这是否意味着一个属性可以是头表的一部分。我们称这里的关键列'header_id。

The alternative to this is a name value pair setup. You want a 'header' table to hold the common values amoungst your products (like name, or price...things that all rpoducts always have). In our example above, you will notice that attribute 'a' is being used on each record...this does mean attribute a can be a part of the header table as well. We'll call the key column here 'header_id'.

第二表是简单地将存储可分配给每个产品的属性和ID分配给它的参考表。我们会打电话与atrr_id表属性的关键。而直前锋，每个属性上面会一行。

Second table is a reference table that is simply going to store the attributes that can be assigned to each product and assign an ID to it. We'll call the table attribute with atrr_id for a key. Rather straight forwards, each attribute above will be one row.

简单的例子：

attr_id, attribute_name, notes
1,b, the length of time the product takes to install
2,c, spare part required
etc...

这只是一个所有属性的列表，这是什么意思的属性。在未来，你将被添加行此表开辟为每头一个新的属性。

It's just a list of all of your attributes and what that attribute means. In the future, you will be adding a row to this table to open up a new attribute for each header.

决赛桌是实际持有信息的映射表。你有你自己的产品ID，属性ID，然后该值。通常被称为详细表：

Final table is a mapping table that actually holds the info. You will have your product id, the attribute id, and then the value. Normally called the detail table:

prd1, b, 5 mins
prd1, c, needs spare jack
prd2, d, 'misc text'
prd3, b, 15 mins

请参阅如何将数据存储为产品密钥，值标签，值？添加任何未来的产品可具有存储在此表中的任何属性的任意组合。添加新的属性是增加一个新行属性表，然后根据需要填充细节表。

See how the data is stored as product key, value label, value? Any future product added can have any combination of any attributes stored in this table. Adding new attributes is adding a new line to the attribute table and then populating the details table as needed.

我beleive有它太... HTTP维基：// EN。 wikipedia.org/wiki/Entity-attribute-value_model

I beleive there is a wiki for it too... http://en.wikipedia.org/wiki/Entity-attribute-value_model

在此之后，它只是搞清楚转动你的数据的最佳方法（我推荐的Postgres这里作为开源数据库选件）

After this, it's simply figuring out the best methodology to pivot out your data (I'd recommend Postgres as an opensource db option here)

这篇关于SQL VS的NoSQL数据，这将是psented到用户$ P $已添加多个过滤器后，的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！