SQL数据库-什么时候使用单独的表与现有表的列? [英] SQL database - when to use a separate table vs a column of an existing one?

问题描述

因此，我通常对SQL和数据库还是很陌生的(只为一个最小的站点设计了一个非常简单的数据库)，并且我正在尝试找出为大量DB驱动的站点设计一些模型的最佳方法.因此，以某位用户上传图库为例.我有一个画廊表，其中包含明智的列，例如上载日期，名称等，并且画廊可以属于一个类别，其中类别不会那么多(最多6个).我应该将类别设为图库表的一列吗?还是有一个单独的类别表，并且类别表和图库表之间存在多对一的关系?我想按照自己的观点进行操作，例如按上传日期对一个类别中的所有画廊进行排序，两者之间在性能/便利性方面有区别吗?使类别成为Gallery表的一列肯定比我更容易处理，但是我不确定什么是最佳实践.谢谢.

So I am pretty new to SQL and databases in general(only designed a very simple one for a minimal site), and I'm trying to work out the best way to design some models for a heavily DB driven site. So take for example, a user uploaded gallery. I have a gallery table with sensible columns like date uploaded, name, etc., and galleries can belong to one category, of which there will not be that many (at most like 6). Should I have the category be a column of the gallery table? Or have a separate table for categories and have a many to one relationship between the category and gallery tables? I would like to do things in my views like sorting all galleries in a category by date uploaded, is there a performance/convenience difference between these? Having the category be a column of the Gallery table certainly seems easier to deal with than me, but I'm not sure what is the best practice. Thanks.

推荐答案

首先，您需要了解概念上的差异.

First of all, you need to understand the conceptual difference.

根据经验，您可以放心考虑以下等效条件:

As a rule of thumb, you are safe to consider the following equivalence:

表~~~实体

列~~~属性

因此，当您需要添加关于实体(现有表)的新数据时，您可以问自己的问题是:

So, when you need to add a new piece of data, in relation to an Entity (an existing Table), the question you can ask yourself is:

这条数据是实体的属性吗?

如果答案是肯定的，那么您需要一个新列.

If the answer is yes, then you need a new column.

例如，假设您有一个描述Student实体的表:

For instance, say you have a table describing the Student entity:

Table Student:

[PK] Id
[FK] IdClass
Name
Surname

说您还要添加每个学生的GPA.显然，这是学生的属性，因此您可以在学生"表中添加GPA列.

Say you want to also add the GPA of each student. This is obviously an attribute of the student, so you can add the GPA column in the Student table.

但是，如果您希望为每个学生定义部门，则会看到该部门不是学生的属性.该系是一个实体，它存在并且具有自己的属性，即 外部 学生的范围.

If however you wish to define the Department for each Student, you will see that the department is not an attribute of a Student. The Department is an entity, it exists and has its own attributes outside the scope of the student.

因此，学生的属性是某个部门的隶属关系，而不是部门本身.

Therefore, the attribute of the student is the affiliation to a certain Department, but not the department itself.

因此，您将创建一个新的Department表，并将 Department.Id 用作Student表中的FK.

So, you will create a new Department table, and use the Department.Id as a FK in the Students table.

我希望这会有所帮助.干杯.

I hope this helps. Cheers.

这篇关于SQL数据库-什么时候使用单独的表与现有表的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！