创建一个表还是两个表? [英] create one table or two table?

问题描述

有关表设计的一个快速问题.

a quick question in regards to table design.

 Type(table 1):  typeId  TypeName
                   1     animal
                   2     fish

 Item(table 2):  itemId itemName itemValue typeId
                   1     horse    abc        1
                   2     pig      def        1
                   3     shark    ghi        2  

我只想创建一张桌子

i want to create only one table

 TypeItem:      itemId  itemName itemValue typeId
                   1     animal   null        0
                   2     fish     null        0
                   3     horse    abc         1
                   4     pig      def         1
                   5     shark    ghi        2 

哪种方法更好?

which way is better?

推荐答案

更好的是这样一个主观术语.我认为第一种方法更好.这样做的原因是因为人们会对您的数据有一个假设.当他们阅读您的餐桌时，他们将期待一种动物/鱼类.如果他们获得了通用名，那可能就不是您想要的.这意味着您将需要从每个结果集中过滤掉这些记录.数据的前两行与其余数据的含义不同.我认为这是个问题.

我认为这使问题变得更加容易，因为您打算以任何一种方式创建联接.因此，使自己的生活更轻松，并且不要在同一张表中混合使用不同类型的数据.

Better is such a subjective term. In my opinion, the first way is better. The reason for this is because of the assumption people will have about your data. When they read your table, they will be expecting a type of animal/fish. If they get the generic, that probably won''t be what you want. That means you will need to filter these records out of every resultset. These first two rows of data don''t mean the same thing as the rest of the data. That is a problem, in my opinion.

I think it makes the issue even easier because you are intending to create a join either way you do this. Therefore, make life easier for yourself and don''t mix your types of data in the same table.

第一个基于数据库设计的标准规则会更好.在大多数情况下，建议使用标准化来更好地处理数据.选项1跟随2-NF.

阅读:数据库规范化基础 [

First one is better based on the standard rule of database design. Most of the times, Normalization is suggested for better data handling. Option 1 follows 2-NF.

Read: Database Normalization Basics[^]

这篇关于创建一个表还是两个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！