良好的数据库设计,可变的属性数量[英] Good database design, variable number of attributes

本文是小编为大家收集整理的关于良好的数据库设计,可变的属性数量的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我正在尝试创建一个包含设备列表数据库.所有设备都将具有某些常见属性(例如制造商,型号,序列号等),然后还有其他特定于某些设备的属性(即,调制解调器将具有访问#,而是太阳能电池板将具有输出能力).我不确定如何使用良好的数据库设计原理来表示这些不断变化的属性,我尝试搜索网络,但是我不确定要搜索什么.

我想出了以下可能的解决方案以及对它们的最初想法:

  1. 有一个大表格,每个可能的属性都将其放置在不适用的位置.显然,这有一些缺陷.

  2. 为每种设备类型都有一个单独的表.如果我想打印所有设备的列表,这似乎是一场噩梦,我怎么知道要查找哪个表?

  3. 有一个带有常见属性的表,以及使用外键访问的每种设备类型的其他表来存储额外的属性.我可能可以做这项工作,但这会很麻烦,只是感觉不错的解决方案.

  4. 一种实体 - 属性值类型模型.只是对我想做的事情似乎不太适合.

我对数据库没有很多经验,因此我在这里学习,与此问题有关的任何链接或"必须阅读"数据库设计的文章都将不胜感激.谢谢!

编辑: 首先,我发现我需要Google"继承映射",这可能会帮助其他有类似问题的人.为了解决问题,我最终使用了#2和#3的混合体.它实际上很容易,运行良好,并解决了添加其他设备类型的问题,而没有EAV的复杂性.感谢所有评论和建议!

推荐答案

选项1、2和3分享一个非常严重的缺陷:当有人梦见新属性时,您必须修改基础表格架.在选项1的情况下,由于引入新的设备类型的可能性,问题加剧了问题.您如何确定该属性始终是固定的?您有多高兴发生停电或告诉客户不,您不能有新属性?

如果您很可能会从常见属性中进行查询,则可以尝试3和4的混合体,而在属性类型上分开时,将2次划分为2,而不是设备类型,这似乎更加易变.选项4(如果我正确理解)是选项1的正常形式版本,该版本解决了其所有固有的问题(稀疏性和脆性).

INVENTORY( id*, model, manufacturer, serial )
ATTRIBUTE( id*, name, type, description )
INVENTORY_FACT_STRING( inv_id*, attr_id*, value )
INVENTORY_FACT_NUMBER( inv_id*, attr_id*, value )
INVENTORY_FACT_LIST_STRING( inv_id*, attr_id*, ordinal*, value )

等.

其他推荐答案

替代方案1、2和3在他的一本书和他的网站上概述了马丁·福勒(Martin Fowler).

单表sashitance(option 1)

concrete table sashitance sashitance(选项2,of)

class table sashitance(option 3)

我的偏爱是选项3.每个人都在一般方案中占有一席之地.

eav accomaties添加新属性非常好.但是,当将数据转换为有用的信息时,EAV数据库可能是一场噩梦.

我有更长的答案,我将按需发布.

其他推荐答案

我认为您面临常规数据库归一化. 您需要:

之类的桌子
Items -> Id, Name, Model, Brand Id
Brands -> Id, Name
Attribute Names -> id, name
Attribute Mappings -> Id, Names Id, Items Id, Attribute Description

如果有一个以上的属性,请在属性表中列出并与产品ID相关联.

数据库归一化

本文地址:https://www.itbaoku.cn/post/597622.html

问题描述

I'm trying to create a database that contains a list of equipment. All of the equipment will have certain common attributes (such as manufacturer, model #, serial #, etc.), then there are other attributes that are specific to a certain piece of equipment (ie, a modem will have an access #, whereas a solar panel will have an output capacity). I'm not sure how to represent these changing attributes with good database design principles, I've tried searching the web, but I'm not entirely sure what to search for.

I've come up with the following possible solutions and my initial thoughts on them:

  1. Have one big table with every possible attribute and just put null where it's not applicable. Obviously this has some flaws.

  2. Have a separate table for each equipment type. This seems like it might be a nightmare to use, if I want to print a list of all the equipment, how do I know which tables to lookup?

  3. Have a table with the common attributes, and other tables for each equipment type accessed with a foreign key to store the extra attributes. I could probably make this work, but it would be cumbersome and just doesn't feel like a very good solution.

  4. An entity-attribute-value type model. Just doesn't seem like a very good fit for what I want to do.

I don't have a lot of experience with databases so I'm learning as I go here, any links relating to this problem or "must read" articles on database design would be appreciated. Thanks!

EDIT: First off, I found out that I needed to Google "Inheritance mapping", that might help anyone else that has a similar question. To solve the problem I ended up using a hybrid of #2 and #3. It was actually pretty easy, works well, and solves the problem of adding additional equipment types without the complexity of EAV. Thanks for all the comments and suggestions!

推荐答案

Options 1, 2, and 3 share one very serious flaw: you have to modify the underlying table schema when someone dreams up a new attribute. In the case of Option 1 the problem is compounded by the possibility that a new equipment type will be introduced. How sure are you that the set of attributes is fixed for all time? How happy will you be to take outages or tell the client that no, you can't have a new attribute?

If you are very likely to do queries off common attributes, you might try a hybrid of 3 and 4, with a dash of 2 thrown in splitting on attribute type rather than equipment type, which seems much more volatile. Option 4, if I understand correctly, is a normal form version of option 1 which solves all its inherent problems (sparseness and brittleness).

INVENTORY( id*, model, manufacturer, serial )
ATTRIBUTE( id*, name, type, description )
INVENTORY_FACT_STRING( inv_id*, attr_id*, value )
INVENTORY_FACT_NUMBER( inv_id*, attr_id*, value )
INVENTORY_FACT_LIST_STRING( inv_id*, attr_id*, ordinal*, value )

etc.

其他推荐答案

Alternatives 1, 2, and 3 are outlined by Martin Fowler in one of his books, and on his website.

Single Table Inheritance (option 1)

Concrete Table Inheritance (option 2, sort of)

Class Table inheritance (option 3)

My preference is option 3. Each one has its place in the general scheme of things.

EAV accomodates adding new attributes on the fly very well. But when it comes time to turn the data into useful information, an EAV database can be a nightmare.

I have a longer answer, which I will post on demand.

其他推荐答案

I think that you faced a regular database normalization. You need tables like:

Items -> Id, Name, Model, Brand Id
Brands -> Id, Name
Attribute Names -> id, name
Attribute Mappings -> Id, Names Id, Items Id, Attribute Description

In case if there is more than one Attribue, list then in Attribute Tables and associate with Product Id etc. Try to come up with 3rd normalized form

Database Normalization