需要一个简单的MySQL数据库设计的提示 [英] Need a tip on simple MySQL db design
问题描述
items
itemId | itemName
-------------------
0001 |胸部
0002 |剑
0003 |头盔
属性（属性查找表）
attributeId | attributeName
---------------------------------
01 |力量
02 |敏捷
03 |智力
04 |防御
05 |损坏
06 | mana
07 |耐力
08 | description
09 |键入
item_attributes（结表）
itemId | attributeId |价值（混合型，坏？）
------------------------------------
0001 | 01 | 35
0001 | 03 | 14
0001 | 09 |装甲
0001 | 08 |精美的精灵制作
0002 | 09 |武器
0002 | 05 | 200
0002 | 02 | 15
0002 | 08 |你的平均剑
0003 | 04 | 9000
0003 | 09 |装甲
0003 | 06 | 250
我的这个设计的问题是 c> item_attributes 表需要使用 varchar 数据类型，因为该值的数据可以是 int ， char ， varchar 。我认为这是一个糟糕的做法，因为我不能够根据特定属性快速排序我的项目。当查询（例如）获取具有15到35之间的属性强度的项目时，它也将遭受性能影响。
这是我的潜在修复。我只是在属性表中添加了一个 data_type 列。所以它看起来像这样
属性（属性查找表）
attributeId | attributeName | data_type
--------------------------------------------- ------
01 |力量| int
09 |类型| char
08 |智力| varchar
然后我再添加3个列到 item_attributes 表， int ， char ， varchar 。以下是新的 item_attributes 表格如何。
item_attributes ）
itemId | attributeId |值| int | char | varchar
--------------------------------------------- ---------------------------
0002 | 09 |武器|空|武器| null
0002 | 05 | 200 | 200 | null | null
0002 | 02 | 15 | 15 | null | null
0002 | 08 |你的平均剑| null | null |您的平均剑
所以现在，如果我要根据其力量对项目进行排序属性，我将使用 int 列。或者根据其描述搜索一个项目，我将搜索 varchar 列。
然而，相信我的设计有点奇怪。现在我必须查找属性表中的 data_type 列，并动态确定
任何输入都将不胜感激。
提前感谢
编辑11/29/2010
这是我的项目的详细列表
----------------------------------- ---
http://wow.allakhazam.com/ihtml?27718
Aldor Defender的Legplates
拾取后绑定
LegsPlate
802 Armor
+21力量
+14敏捷
+21耐力
物品等级99
装备：提高命中等级14.
---------- ----------------------------
http://wow.allakhazam.com/ihtml?17967
精炼Onyxia规模
皮革
物品等级60
------------------------------- -------
http://wow.allakhazam.com/ihtml?27719
阿波尔护身符
拾取后绑定
LegsLeather
202护甲
+15敏捷
+21耐力
物品等级99
装备：攻击强度提高28点。
装备：将命中等级提高20点。
---------------------------------- ----
http://wow.allakhazam.com/ihtml?5005
Emberspark吊坠
装备后绑定
颈部杂项
+2耐力
+7精神
需要等级30
物品等级35
--------------------------- -----------
http://wow.allakhazam.com/ihtml?23234
敏捷的蓝色Bryanite
宝石
需要等级2
物品等级10
+8敏捷
-------------------------------- ------
http://wow.allakhazam.com/ihtml?32972
啤酒眼镜
拾取后绑定
唯一
头部杂项
物品等级10
装备：由Belbi Quikswitch保证让每个人都看起来有吸引力！
--------------------------------------
http：// wow.allakhazam.com/ihtml?41118
Gadgetzan礼物
拾取后绑定
独特
物品等级5
请返回季节主办单位
--------------------------------------
http：//wow.allakhazam .com / ihtml？6649
Searing Totem Scroll
唯一
任务物品
需要等级10
物品等级10
使用：
--------------------------------------
http：// wow.allakhazam.com/ihtml?6648
Stoneskin图腾滚动
唯一
任务物品
需要等级4
物品等级4
使用： ;
--------------------------------------
http：// wow.allakhazam.com/ihtml?27864
Brian的Bryanite延长成本复制
宝石
物品等级10
宝石测试附魔
-------- ------------------------------
< h2>编辑＃2
- 这10个例子不代表我收集的所有35316项数据。
- 颈部杂项意味着该项目属于颈部和杂项两种类型。
- 不要在Action中读取太多东西，只是追求描述
- 当一个项目说装备：攻击强度提高28点，这意味着+28攻击强度的玩家角色。它与+15敏捷度相同。
- 共有241884个一对多项目属性记录，所以大概是241884 / 35316〜= 8个项目的平均属性。此外，数据从网站挖掘成一个巨大的文本文件。没有良好的信息来标识项目的类型或类别。所以如果剑一词出现在第3行或第4行，就会被自动分类为剑。
- 该项目可能会在每次新游戏更新中更改。
- 除name之外，项目之间没有共享的通用属性。 li>
- 项目数据可通过网络应用程序访问。不清楚位和向量的含义？
- 在数据挖掘阶段使用正则表达式来清理特殊字符并搜索特定关键字，以便对项目进行分类。还要提取属性名称和值。例如，+15敏捷性将字符串敏捷性作为属性名称提取，15将作为值。 （对于问题6和6.1，我不太了解Slog代表服务器日志在这里？将正则表达式转换为SQL？）
a href =http://www.cityofqin.com/model.pdf =nofollow>
模型图
以下是一个示例，查询如何看起来像
从itemattributestat
中选择*
其中item_itemId = 251
item_itemId | attribute_attributeId |值| listOrder
========================================= ==========
'251'，'9'，'0'，'1'
'251'，'558'，'0'，'2'
'251'，'569'，'0'，'3'
'251'，'4'，'802'，'4'
'251'，'583'，'21 '，'5'
'251'，'1'，'14'，'6'
'251'，'582'，'21'，'7'
' ，'556'，'99'，'8'
'251'，'227'，'14'，'9'
列表顺序是为了跟踪哪个属性应该首先列出。对于格式化目的
创建视图itemDetail为
选择Item_itemId作为id，i.name作为项目，a.name作为属性， $（$ item）
通过Item_itemId asc，listOrder asc;
，由$（$）
$（$）
从（（itemattributestat join item as i on Item_itemId = i.itemId））
将属性作为Attribute_attributeId = a.attributeId
上述视图生成以下内容
从itemdetail
中选择*
其中id = 251;
id |项目|属性|价值
'251'，'Aldor Defender'的Legplates'，'拾取后绑定'，'0'
'251'，'Aldor Defender'的Legplates'，'Legs' 0'
'251'，'Aldor Defender'''''''''''0'
'251'，'Aldor Defender''Legament'''Armour'
'251'，'Aldor Defender'的Legplates'，'力量'，'21'
'251'，'Aldor Defender'的Legplates'，'Agility'，'14'
'251'，'Aldor Defender''Legal''''耐力'，'21'
'251'，'Aldor Defender'的Legplates'，'Item Level'，'99'$ b $ '''''''''''''''''''''''''$' >值为0的属性表示属性名称表示项目类型。'装备：通过@@。'，'14'提高命中评分@@是占位符，浏览器上的已处理输出将为装备：提高命中等级14。'解决方案为什么你有一个
属性表？
属性是列，而不是表。
网站链接告诉我们什么。
数据库的整个想法是，根据需要，为每个查询加入了许多小表，所以你需要习惯这个。当然，它给你一个网格，但一个短而甜蜜的，没有空。你想做的是避免表;只需一个大量的网格，它们就是空的。
（snip）
不要在属性名（列名）前加上表名，这是冗余的。当您开始编写使用多个表的SQL时，这将变得很清楚：然后可以使用表名或别名对任何不明确的列名进行前缀。
异常是完全呈现的PK，并且以FK形式使用。
浏览网站并阅读一些SQL问题。 / p>
此后，您可以考虑如果您想要
力量和防御为属性（列）type;或不。
回复评论30 Nov 10
。
非常好，你了解您的数据对。现在我明白为什么你有一个属性表。
请确保这10个例子是有代表性的，我正在密切关注他们。
- 类型：宝石名称：Emberspark吊坠...或者，是NeckMiscellaneous类型？
- 唯一一个真正的ItemType？我认为没有
- Action.Display请返回一个季节主办单位
- Attack在
AttackPower和HitRating？
。
有几种不同类型的项目（35,000），我的产品集群示例。另一种说明这个问题的方法是，有多少变化。我的意思是意思是不是3500项目÷8属性？
item_attributes会否更改而不使用/ / em发布s / w（例如一个新的
Inner Strength属性）？
每个项目，什么属性重复（多个）;到目前为止，我只看到Action吗？
这是一个游戏，所以你需要一个紧凑而非常快的数据库，也许是完全记忆的居民。没空没有任何东西。最短的数据类型不要重复任何事情（不要重复你自己）。您是否对位（布尔）和向量感到满意？
您是否需要轻松地将这些正则表达式转换为SQL，或者您对每个（即，一旦你让他们在SQL中工作，他们是非常稳定的，然后你不要弄乱它，除非你发现一个bug）（没有讽刺，严重的问题）？
6.1或者也可能是另一回事：数据库是磁盘驻留的;你把它加载到内存中一次;您在游戏中运行正则表达式;偶尔写磁盘。因此，无需将正则表达式转换为SQL？
这是一个数据模型，我正在哪里，这一切都不确定;它将被你的答案调制。要清楚：
-
第六个普通表单是行由主键组成，最多包含一个属性
-
我已经绘制（6.1）不（6），因为你的数据增强了我的信念，你需要一个纯粹的6NF关系数据库
-
我的 产品群集数据模型 ，比EAV更好的例子是6NF，然后通过DataType再次Normalize（不是Normal Form意义上的），以减少表的数量，你已经看过（EAV人通常会去一个或几个巨大的桌子。）
-
这是5NF，只有6NF右侧的2个表。 p>
链接到IDEF1X符号 对于不熟悉关系建模的人标准。
回复编辑＃2 05 Dec 10
1.1。好的，更正了。
1.2。然后IsUnique是Item的指标（布尔值）。
1.3。行动。我明白。那么你要存储哪里？
1.4。颈部杂项意味着该项目属于颈部和 Misc 的两个类别。这意味着两个分开 Item.Name = Emberspark Pendant ，每个都有不同的类别。
。
2所以你需要快速快速记忆驻留数据库。这就是为什么我试图让你跨越GridLand，到RelationalLand。
。
3.好的，我们保持第五普通表，不需要6NF或产品集群（每个数据类型的表）。 Sofar 值都是整数。
。
4.我可以看到：级别， RequiredLevel ， IsUnique ， BindsPickedUp ， BindsEquipped 。
。
5.位是布尔值{0 | 1}。 （关系）投影需要向量。我们稍后会给他们。
。
6.好的，你已经解释过，你没有将正则表达式翻译成SQL。 （Slog意味着艰苦的劳动）。
。
7.什么是Category.ParentId？父类别？以前没有出现过。
。
8. Attribute.GeneratedId？
请评估数据模型（更新）。我还有更多的列，除了你在你的。如果在数据模型中有什么不明白的，请问一个具体的问题。你已经阅读了符号文档，对吧？
我有 Action 作为表，其中 ItemAction 持有价值：
装备：将攻击强度提高28点是 Action.Name = 增加攻击强度和 ItemAction.Value = 28。 / p>
I am trying to make a simple item database using MySQL for a game. Here is what my 3 tables would look like
items
itemId | itemName
-------------------
0001 | chest piece
0002 | sword
0003 | helmet
attributes (attribute lookup table)
attributeId | attributeName
---------------------------------
01 | strength
02 | agility
03 | intellect
04 | defense
05 | damage
06 | mana
07 | stamina
08 | description
09 | type
item_attributes (junction table)
itemId | attributeId | value (mixed type, bad?)
------------------------------------
0001 | 01 | 35
0001 | 03 | 14
0001 | 09 | armor
0001 | 08 | crafted by awesome elves
0002 | 09 | weapon
0002 | 05 | 200
0002 | 02 | 15
0002 | 08 | your average sword
0003 | 04 | 9000
0003 | 09 | armor
0003 | 06 | 250
My problem with this design is that value column in item_attributes table needs to use varchar data type, since the value's data can be int, char, varchar. I think this is a bad approach because I would not be able to quickly sort my items based on particular attributes. It would also suffer performance hit when a query such as get items with attribute strength that has value between 15 and 35 is processed.
Here is my potential fix. I simply added a data_type column to the attributes table. So it would look something like this
attributes (attribute lookup table)
attributeId | attributeName | data_type
---------------------------------------------------
01 | strength | int
09 | type | char
08 | intellect | varchar
Then I would add 3 more columns to item_attributes table, int, char, varchar. Here is how the new item_attributes table would look like.
item_attributes (junction table)
itemId | attributeId | value | int | char | varchar
------------------------------------------------------------------------
0002 | 09 | weapon | null |weapon| null
0002 | 05 | 200 | 200 | null | null
0002 | 02 | 15 | 15 | null | null
0002 | 08 | your average sword | null | null | your average sword
So now if I were to sort items based on its strength attribute, I would use int column. Or search for an item based on its description, I would search the varchar column.
I still, however, believe my design is a bit weird. Now I would have to look up the data_type column in attribute table and dynamically determine which column in item_attributes table is relevant to what I am looking for.
Any inputs would be greatly appreciated.
Thanks in advance.
EDIT 11/29/2010 Here is a detailed list of my items
-------------------------------------- http://wow.allakhazam.com/ihtml?27718 Aldor Defender's Legplates Binds when picked up LegsPlate 802 Armor +21 Strength +14 Agility +21 Stamina Item Level 99 Equip: Improves hit rating by 14. -------------------------------------- http://wow.allakhazam.com/ihtml?17967 Refined Scale of Onyxia Leather Item Level 60 -------------------------------------- http://wow.allakhazam.com/ihtml?27719 Aldor Leggings of Puissance Binds when picked up LegsLeather 202 Armor +15 Agility +21 Stamina Item Level 99 Equip: Increases attack power by 28. Equip: Improves hit rating by 20. -------------------------------------- http://wow.allakhazam.com/ihtml?5005 Emberspark Pendant Binds when equipped NeckMiscellaneous +2 Stamina +7 Spirit Requires Level 30 Item Level 35 -------------------------------------- http://wow.allakhazam.com/ihtml?23234 Blue Bryanite of Agility Gems Requires Level 2 Item Level 10 +8 Agility -------------------------------------- http://wow.allakhazam.com/ihtml?32972 Beer Goggles Binds when picked up Unique HeadMiscellaneous Item Level 10 Equip: Guaranteed by Belbi Quikswitch to make EVERYONE look attractive! -------------------------------------- http://wow.allakhazam.com/ihtml?41118 Gadgetzan Present Binds when picked up Unique Item Level 5 "Please return to a Season Organizer" -------------------------------------- http://wow.allakhazam.com/ihtml?6649 Searing Totem Scroll Unique Quest Item Requires Level 10 Item Level 10 Use: -------------------------------------- http://wow.allakhazam.com/ihtml?6648 Stoneskin Totem Scroll Unique Quest Item Requires Level 4 Item Level 4 Use: -------------------------------------- http://wow.allakhazam.com/ihtml?27864 Brian's Bryanite of Extended Cost Copying Gems Item Level 10 gem test enchantment --------------------------------------
EDIT #2
- These 10 examples are not representative of all 35316 items data that I have collected.
- NeckMiscellaneous means that item is in both categories of `Neck` and `Misc`.
- Unique means the only one item can be used on character.
- Don’t read too much into the "Action", they are just quest description
- When an item says `Equip: increase attack power by 28` it just means +28 attack power on the player character. It is the same as +15 agility.
- There are a total of 241884 one-to-many item-attribute records, so that comes about to 241884/35316 ~= 8 average attributes per item. Also the data is mined from the website into a gigantic text file. There is NO "well formed" information to identify an item’s type or category. So if the word "sword" appears on either 3rd or 4th line, it is automatically categorized as sword.
- The item might get changed on each new update of the game.
- There is no universal attribute shared amongst the item besides `name`
- The item data is accessible through a web app. Unclear about what you mean by bits and vectors?
- The regular expression is used during data mining stage to clean up the special character and search for specific keyword in order to categorize the items. Also to extract attribute name and value. For example, +15 agility would have string agility extracted as attribute name and 15 as value. (I don’t understand much about question 6 and 6.1. Slog stands for server log here? Translate regexes to SQL?)
Here is an example how a query looks like
select * from itemattributestat where item_itemId=251 item_itemId | attribute_attributeId | value | listOrder ======================================================= '251', '9', '0', '1' '251', '558', '0', '2' '251', '569', '0', '3' '251', '4', '802', '4' '251', '583', '21', '5' '251', '1', '14', '6' '251', '582', '21', '7' '251', '556', '99', '8' '251', '227', '14', '9'
The list order is here to keep track of which attribute should be listed first. For formatting purpose
create view itemDetail as
select Item_itemId as id, i.name as item, a.name as attribute, value
from ((itemattributestat join item as i on Item_itemId=i.itemId)
join attribute as a on Attribute_attributeId=a.attributeId)
order by Item_itemId asc, listOrder asc;
The above view produces the following with
select * from itemdetail where id=251; id | item | attribute | value '251', 'Aldor Defender''s Legplates', 'Binds when picked up', '0' '251', 'Aldor Defender''s Legplates', 'Legs', '0' '251', 'Aldor Defender''s Legplates', 'Plate', '0' '251', 'Aldor Defender''s Legplates', 'Armor', '802' '251', 'Aldor Defender''s Legplates', 'Strength', '21' '251', 'Aldor Defender''s Legplates', 'Agility', '14' '251', 'Aldor Defender''s Legplates', 'Stamina', '21' '251', 'Aldor Defender''s Legplates', 'Item Level', '99' '251', 'Aldor Defender''s Legplates', 'Equip: Improves hit rating by @@.', '14'
An attribute with value 0 means the attribute name represents the item type. 'Equip: Improves hit rating by @@.', '14' @@ is place holder here, a processed output on a browser will be 'Equip: Improves hit rating by 14.'
Why do you have an attribute table ?
Attributes are columns, not tables.
The website link tells us nothing.
The whole idea of a database is that you join the many small tables, as required, for each query, so you need to get used to that. Sure, it gives you a grid, but a short and sweet one, without Nulls. What you are trying to do is avoid tables; go with just one massive grid, which is full of Nulls.
(snip)
Do not prefix your attribute names (column names) with the table name, that is redundant. This will become clear to you when you start writing SQL which uses more than one table: then you can use the table name or an alias to prefix any column names that are ambiguous.
The exception is the PK, which is rendered fully, and used in that form wherever it is an FK.
Browse the site, and read some SQL questions.
After doing that, later on, you can think about if you wantstrength and defense to be attributes (columns) of type; or not. Et cetera.
Responses to Comments 30 Nov 10
.
Excellent, you understand your data. Right. Now I understand why you had an Attribute table.
Please make sure those 10 examples are representative, I am looking at them closely.
- Type:Gem Name:Emberspark Pendant ... Or, is NeckMiscellaneous a type ?
- Is Unique a true ItemType ? I think Not
- Action.Display "Please return to a Season Organizer"
- Where are the Attrinutes for
AttackPowerandHitRating?
.
How many different types of items (of 35,000) are there, ala my Product Cluster example. Another way of stating that question is, how many variations are there. I mean, meaningfully, not 3500 Items ÷ 8 Attributes ?
Will the item_attributes change without a release of s/w (eg. a new
Inner Strengthattribute) ?Per Item, what Attributes are repeating (more than one); so far I see only Action ?
It is a game, so you need a db that is tight and very fast, maybe fully memory resident, right. No Nulls. No VAR Anything. Shortest Datatypes. Never Duplicate Anything (Don't Repeat Yourself). Are you happy with bits (booleans) and vectors ?
Do you need to easily translate those regexes into SQL, or are you happy with a serious slog for each (ie. once you get them working in SQL they are pretty stable and then you don't mess with it, unless you find a bug) (no sarcasm, serious question) ?
6.1 Or maybe it is the other way round: the db is disk-resident; you load it into memory once; you run the regexes on that during gameplay; occasionally writing to disk. Therefore there is no need to translate the regexes to SQL ?
Here's a Data Model of where I am heading, this not at all certain; it will be modulated by your answers. To be clear:
Sixth Normal Form is The Row consists of the Primary Key and, at most, one Attribute.
I have drawn (6.1) not (6), because your data reinforces my belief that you need a pure 6NF Relational database
My Product Cluster Data Model, the better-than-EAV example, is 6NF, then Normalised again (Not in the Normal Form sense) by DataType, to reduce no of tables, which you have already seen. (EAV people usually go for one or a few gigantic tables.)
This is straight 5NF, with only the 2 tables on the right in 6NF.
Link to IDEF1X Notation for those who are unfamiliar with the Relational Modelling Standard.
Response to Edit #2 05 Dec 10
1.1. Ok, corrected.
1.2. Then IsUnique is an Indicator (boolean) for Item.
1.3. Action. I understand. So Where are you going to store it ?
1.4. NeckMiscellaneous means that item is in both categories of Neck and Misc. That means two separate Item.Name=Emberspark Pendant, each with a different Category.
.
2. and 5. So you do need fast fast memory-resident db. That's why I am trying to get you across the line, away from GridLand, into RelationalLand.
.
3. Ok, we stay with Fifth Normal Form, no need for 6NF or the Product Cluster (tables per Datatype). Sofar the Values are all Integers.
.
4. I can see additionally: Level, RequiredLevel, IsUnique, BindsPickedUp, BindsEquipped.
.
5. Bits are booleans { 0 | 1 }. Vectors are required for (Relational) projections. We will get to them later.
.
6. Ok, you've explained, You are not translating regular expressions to SQL. (Slog means hard labour).
.
7. What is Category.ParentId ? Parent Category ? That has not come up before.
.
8. Attribute.GeneratedId ?
Please evaluate the Data Model (Updated). I have a few more columns, in addition to what you have in yours. If there is anything you do not understand in the Data Model, ask a specific question. You've read the Notation document, right ?
I have Action as a table, with ItemAction holding the Value:
Equip: increase attack power by 28 is Action.Name=Increase attack power by and ItemAction.Value=28.
这篇关于需要一个简单的MySQL数据库设计的提示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
