设计数据库：哪个是更好的方法？ [英] Designing a database : Which is the better approach?

问题描述

我正在设计一个数据库，我想知道我应该使用哪种方法。我将描述我打算设计的数据库和可用于将数据存储在表中的可能方法。

请推荐我应该使用哪种方法，为什么？

关于数据：

强>我有七个属性，需要照顾。这些只是例子，而不是我打算存储的实际。让我叫他们：

1）名称

2）DOB年龄

3）性别

4）婚姻状况

5）工资

6）母亲

p>

B）表格中至少有10000行，他们可以长期从那里开始

C）属性的数量可以在一段时间内更改。也就是说，可以将新属性添加到现有数据集。

方法1

创建具有7个属性的表，并按原样存储数据。

• Pro：更容易阅读资料和资讯




• Con：对于值未知的某些属性，某些行可能有许多空值。

方法2

表有3个属性。让它们被调用：

1）Attr_Name：存储属性名称。例如姓名，年龄，性别..etc

2）Attr_Value：存储上述属性的值，例如：Tom，25，Male

3）唯一ID：唯一标识数据库中的名称，值对。例如。 SSN

因此，在方法2中，如果需要为某些行添加新属性，我们可以将它们添加到我们创建的hashmap中，而不必担心空值

• Pro：Hashmap结构。消除null。




• Con：数据不容易阅读。

  
  $ b

  这是更好的方法。

  
  
  

  我觉得方法1是更好的方法。因为它不太难处理空值和数据是组织良好，它容易掌握这个数据的王国。请建议我应该使用哪种方法，为什么？

  
  
  

  谢谢！

  解决方案

  p>您的第二个选项是最糟糕的设计错误之一。这应该只有当你有成百上千的属性不断变化，并从对象到对象（例如医学实验室测试）是不一样的。如果你需要这样做，那么在任何情况下都不要使用关系数据库来做到这一点。 NOSQL数据库句柄EAV设计比关系数据库更好。

  
  
  

  设计2的另一个问题是，几乎不可能有良好的数据完整性，因为您无法正确实施FK和数据类型，并对数据添加约束。因为这个东西不会被设计成只发生在应用程序中，因为除了应用程序之外的事情通常影响数据，这个因素本身就足以让你的第二个想法愚蠢和愚蠢。

  
  
  

  第一个设计一般会表现更好。它将更容易编写查询，它会迫使您考虑在添加属性时需要更改（这是一个加号而不是减号），而不是设计为始终显示所有属性，无论您是否需要它们。如果你有很多null，那么添加一个相关的表，而不是更多的列（你可以有一对一的相关表）。通常在这种情况下，你可能有一些你只知道一个记录的子集将有，他们通常被分组的主题很自然。例如，您可能具有属于一个表中的一般人员相关属性（名称，电话，电子邮件，地址）。然后，您可能具有属于单独表中的学生相关属性和属于第三个表的与教师相关的属性。或者你可能需要所有的保险政策和车辆保险，健康保险，房子保险和人寿保险的单独表格。

  
  
  

  有第三种设计可能性。如果你有一组属性，你知道前面然后把它们放在一个表中，并有一个EAV表只为属性，不能在设计时确定。当应用程序想要让用户灵活地添加客户特定的数据字段时，这是通用模式。

  
  

  I am designing a database and am wondering which approach should I use. I am going to describe the database I intend to design and the possible approaches that I can use to store the data in the tables.

  Please recommend which approach I should use and why?

  About the data:

  A) I have seven attributes that need to be taken care of. These are just examples and not the actual ones I intend to store. Let me call them:

  1)Name

  2)DOB (Modified..I had earlier put in age here..)

  3)Gender

  4)Marital Status

  5)Salary

  6)Mother Tongue

  7)Father's Name

  B) There will be a minimum of 10000 rows in the table and they can go up from there in the long term

  C) The number of attributes can change over the period of time. That is, new attributes can be added to the existing dataset. No attributes will ever be removed.

  Approach 1

  Create a table with 7 attributes and store the data as it is. Added new columns if and when new attributed need to be added.

  • Pro: Easier to read the data and information is well organized

  • Con: There can be a lot of null values in certain rows for certain attributes for which values are unknown.

  Approach 2

  Create a table with 3 attributes. Let them be called :

  1) Attr_Name : Stores the attribute name . eg name,age,gender ..etc

  2) Attr_Value :Stores value for the above attribute, eg : Tom, 25, Male

  3) Unique ID : Uniquely identifies the Name, Value pair in the database. eg. SSN

  So, in approach 2, in case new attributes need to be added for certain rows, we can just add them to the hashmap we have created without worrying about null values.

  • Pro: Hashmap structure. Eliminates nulls.

  • Con: Data is not easy to read. Information cannot be easily grasped.

  C) The Question

  Which is the better approach.?

  I feel that approach 1 is the better approach. Because its not too tough to handle null values and data is well organized and its easy to grasp this king of data. Please suggest which approach I should use and why?

  Thanks!

  解决方案

  Your second option is one of teh worst design mistakes you can make. This should only be done when you have hundreds of attributes that change constantly and are in no way the same from object to object (such as medical lab tests). If you need to do that, then do not under any circumstances use a relational database to do it. NOSQL database handle EAV designs better by far than relational ones.

  Another problem with design 2 is that it becomes almost impossible to have good data integrity as you cannot correctly enforce FKs and data types and add contraints to the data. Since this stuff shoudl never be designed to happen only in the application since things other than the application often affect the data, this factor alone is enough to make your second idea foolish and foolhardy.

  The first design will perform better in general. It will be easier to write queries and it will force you to think about what needs to change when you add an attribute (this is a plus not a minus) instead of having to design to always show all attributes whether you need them or not. If you would have a lot of nulls, then add a related table rather than more columns(you can have one-to-one related tables). Usually in this case you might have something that you know only a subset of the records will have and they often fall into groupings by subject fairly naturally. For instance you might have general people related attributes (name, phone, email, address) that belong in one table. Then you might have student-related attributes that belong in a separate table and teacher-related attributes that belong in a third table. Or you might have things you need for all insurance policies and separate tables for vehicle insurance, health insurance, House insurance and life insurance.

  There is a third design possibility. If you have a set of attributes you know up front then put them in one table and have an EAV table only for attributes that cannot be determined at design time. This is the common pattern when the application wants to have the flexibility for the user to add customer specific data fields.

  这篇关于设计数据库：哪个是更好的方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！