使用类似的数据类型归一化各种属性 [英] Normalizing a variety of properties with similar data types

问题描述

我正在设计一个数据库以容纳与游戏相关的属性，例如是否支持多人游戏，它是什么类型，发布日期等。

似乎为每个类别类型创建两个附加表（例如，类型，genres_data）并不像现在那样动态。我最初的想法是设置两种方式之一...

拥有一个包含骨架信息的游戏表，然后是一个列表所有属性的属性表，一个第三个表包含与游戏相关的所有数据，其中基本上仅使用与每个属性相关的列：

 游戏
 ----------- 
 game_id 
 ...相关数据
 
属性
 --------- -  
 property_id 
 title 
 type 
 category 
 
 properties_data 
 --------------- 
 game_id 
 property_id 
 bool 
最小
最大
日期
文本（最大255）
 longtext 
 
 属性
 -------------- 
 property_id 
 title 
 type 
 category 
 column_name 
 
 properties_data 
 ---------------- 
 game_id 
 title 
 description 
 release_date_au 
 release_date_jp 
 genre_rpg 
 genre_fps 
 platform_360 
 platform_ps3 
 platform_pc 
 has_leaderboards 
 has_downloadable_content 
 ... etc 
  
在这种情况下，实际的方法是什么你有几十个类型的数据与一行相关，但是在每个类别中有大量的类别和支持属性？为每个属性类型或类别（对我）制作表格似乎没有效率。
解决方案
这似乎是典型的Supertype-subtype表的簇。除非你没有确定它。所以，正式地识别它，并使数据规范化。
 
 
 
 
 
在超类型（父类）中放置公共列
 
 
将特定于每个子类型的所有列放在单独的子表中
 
 
，以便最终没有可选列
 
 
那么你需要另一个级别的子表。
 
 
 
 
 
如果你去动态填充表，你将失去所有的静态控制数据库中的表与DDL中定义的业务规则（不是代码）。在这种情况下，请注意，这些松散定义的表格是以无数据完整性的形式而闻名的。阅读这个最近的答案来获得一些上下文。
 
 
 
  链接到相关答案  
 
 
 
关键是，如果你做这个动态的东西，请妥善地。
 
 
 
但我不相信你需要走远。使用普通的Rdb，您可以保持充分的关系能力和灵活性。更多的表是Rdb的性质，没有什么可怕的。做得好，你将有完全的控制和速度。这需要我回到第一段。
 
I'm working on setting up a database to house properties related to a game, for example whether or not it supports multiplayer, what genres it is, release dates, etc.

It seems like creating two additional tables (genres, genres_data for example) for each category type isn't nearly as dynamic as it could be. My initial thought was to set it up one of two ways...

Have a games table with skeletal information, then a properties table to list all the properties, and a third table to contain all the data related to the game, where essentially only the columns related to each property are used:
games
-----------
game_id
... relevant data

properties
-----------
property_id
title
type
category

properties_data
---------------
game_id
property_id
bool
min
max
date
text(max255)
longtext
Or, have the games table the same, and have properties include a column name then use columns in the third table:
properties
--------------
property_id
title
type
category
column_name

properties_data
----------------
game_id
title
description
release_date_au
release_date_jp
genre_rpg
genre_fps
platform_360
platform_ps3
platform_pc
has_leaderboards
has_downloadable_content
... etc
What's the practical approach in this sort of scenario where you have a half dozen or so types of data related to a row, but a large number of categories and supporting properties in each category? It doesn't seem efficient to make a table for each property type or category (to me).
解决方案
This appears to be a typical Supertype-subtype cluster of tables. Except you have not identified it as such.  So, identify it formally, and normalise the data.


place common columns in the Supertype (parent)
place all columns specific to each Subtype in a separate child table
so that you end up with no optional columns
if you do, then you need another level of sub-table.


If you go for "dynamically" populated tables, you lose all the control of static tables in a db with the busienss rules defined in DDL (not code).  In that case, be aware that these loosely defined tables are famous for ending up as a mess with no data integrity.  Read this recent answer to get some context.

Link to Related Answer

The point is, if you do that "dynamic" stuff, do it properly.

But I do not believe you need to go that far.  With an ordinary Rdb, you can maintain full relational power and flexibility.  More tables are the nature of an Rdb, nothing to be scared of.  Done properly, you will have full control, and speed.  Which takes me back to the first para.

                        
这篇关于使用类似的数据类型归一化各种属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！