数据库规范化链接 [英] database normalization chaining
问题描述
我有多个表链接，如下所示：
I have multiple table chaining like this:
table1
product_id SERIAL NOT NULL,
name varchar,
table2名称，但可以是不同的颜色）
table2 (separate from table1 because same product name but can be different color )
table2_id
product_id integer,
color varchar,
FOREIGN KEY (product_id) REFERENCES table1 (product_id) ON DELETE CASCADE
table3颜色，但可以不同大小）
table3 (separate from table2 because same product color but can be different size)
table3_id
table2_id integer,
size varchar,
FOREIGN KEY (table2_id) REFERENCES table2 (table2_id) ON DELETE CASCADE
例如产品数据可以是
a chair (name) - red (color) - 100cm(size)
a chair (name) - red (color) - 200cm(size)
b chair (name) - green (color) - 100cm(size)
b chair (name) - green (color) - 200cm(size)
c chair (name) - black (color) - s(size)
c chair (name) - black (color) - m(size)
d chair (name) - black (color) - null(size)
e chair (name) - gold (color) - big(size)
e chair (name) - gold (color) - small(size)
如果遵循标准化删除重复，所以我将它们分开为3表，但我不确定链接这个正确或不
if follow the normalization remove the duplicate so I separate them as 3 table but I'm not sure chaining like this proper or not
推荐答案
似乎也许你认为你需要一个id，每次一个给定的值的子元组出现不止一次在表中。也就是说，您试图删除不需要删除的重复。出现多次的相同值在归一化意义上不是重复，并且也不一定是坏的。也不是你使用多个表也不是ids是标准化。 （我已经尝试解决此表格定义链接反模式此处。）
It seems like maybe you think that you need an id every time a given subtuple of value appears more than once in a table. Ie that you are trying to remove "duplicates" that do not need to be removed. The same value appearing more than once is not a "duplicate" in the normalization sense and is not necessarily bad, either. Also neither your use of multiple tables nor of ids is normalization. (I have already tried to address this table definition "chaining" anti-pattern here.)
从此答案：
问题是，一个公共汽车在
不同小时的同一站点停了几次，我如何存储这个信息避免
冗余？ / p>
The problem is that a bus stops several times at the same stop in different hours, how could I store this information avoiding redundancy?
没有这样的问题。子行可以在表中多次出现，无论是否存在冗余。虽然可以用ids加上另一个表格来替换出现多次的子行，但对于同一个查询结果，我们需要更多的连接。 href =http://stackoverflow.com/a/27809411/3404097>此答案。）
There is no such problem. A subrow can appear more than once in a table whether or not there is "redundancy". (Whatever you think that means. Although one can replace subrows that appear multiple times by ids plus another table, then one needs more joins for the same query result. See this answer.)
或从回答的链接：
这与规范化无关。规范化从不创建新的列名称。 我不想任天堂被重复是错误的。值出现在多个地方没有什么错。查看sqlvogel&我自己此处。
冗余与显示的值无关在多个地方。它是关于多行说明应用程序相同的事情。当使用这样的设计时，有两个基本问题：说某些事情涉及多行（而标准化版本只涉及一行）;并且没有办法只说一次（正常化可以帮助）的一个事情。如果你做关于任天堂的两个不同的独立的陈述，那么你需要两个表和任天堂在每一个提到。有关应用程序的行的说明，请参阅此。 （和搜索我的其他答案re表的语句或标准。）规范化有助于，因为它替换表的行状态的形式... AND ...的其他表，说明...请分别查看此和此（正常化通常被错误地认为涉及或包括避免多个相似列，避免其值具有重复结构的列和/或用ids替换字符串，但是尽管这些可以是好的设计思想，但它们不是标准化的。
"Redundancy" is not about values appearing in multiple places. It is about multiple rows stating the same thing about the application. When using a design like that there are two basic problems: to say certain things multiple rows are involved (while the normalized version involves just one row); and there is no way to say just one of the things at a time (which normalization can help with). If you make two different independent statements about Nintendo then you need two tables and Nintendo mentioned in each one. Re rows making statements about the application see this. (And search my other answers re a table's "statement" or criterion".) Normalization helps because it replaces tables whose rows state things of the form "... AND ..." by other tables that state the "..." separately. See this and this. (Normalization is commonly erroneously thought to involve or include avoiding multiple similar columns, avoiding columns whose values have repetitive structure and/or replacing strings by ids, but although these can be good design ideas they're not normalization.)
如果SUBJECT_MODULE是行，其中[SUBJECT_NAME]有[MODULE_ID]标识[MODULE_NAME]可能有多个模块，然后你必须有多个提及该主题（可能通过其名称）和提及不同的模块（可能通过名称或id）。这不会涉及冗余。
If SUBJECT_MODULE is rows where "[SUBJECT_NAME] has [MODULE_NAME] identified by [MODULE_ID]" and a subject might have more than one module then somewhere you must have multiple mentions of that subject (perhaps via its name) with mentions of different modules (perhaps by name or id). That would not involve redundancy.
这篇关于数据库规范化链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
