Situation:
I have 2 tables:
我有2张桌子:
Table 1
TrackID PK
random columns
ActionID FK
Table 2
ActionID PK
random columns
TrackID FK
The Question: Is there a problem with the above set if so what (normalization?) or is more information needed...?
问题:上述设置是否存在问题(规范化?)或需要更多信息......?
Thanks
Suge
2 个解决方案
#1
The provided schema models two entities being connected by two different relations.
提供的模式模拟由两个不同关系连接的两个实体。
The first relation R1 Track - * ---- 1 - Action is modeled by the field ActionID in Table 1
第一个关系R1 Track - * ---- 1 - Action由表1中的字段ActionID建模
The second relation R2 Track - 1 ---- * - Action is modeled by the field TrackID in Table 2.
第二个关系R2 Track - 1 ---- * - Action由表2中的TrackID字段建模。
This could be a valid schema if your requirements indicate that you have two different relations.
如果您的要求表明您有两种不同的关系,那么这可能是一个有效的模式。
A simplified example of this case would include two entities Employee and Project with
这种情况的简化示例包括两个实体Employee和Project with
the relation
manages Employee - 1 ---- * - Project
管理员工 - 1 ---- * - 项目
and the relation
和关系
works Employee - * ---- 1 - Project
工作员工 - * ---- 1 - 项目
assuming that an employee works only for at most one project.
假设一名员工最多只能为一个项目工作。
The question here is do you want to model one or two relationships?
这里的问题是你想建模一两个关系吗?
If you want to model one relationship between your entities then your schema is somewhat wrong, otherwise it is right.
如果你想在你的实体之间建立一个关系模型,那么你的模式有些错误,否则就是对的。
#2
Is there a problem with the above set?
上面的设置有问题吗?
No, there is no "problem" in the classical meaning of the word. You can stick to your design and it will have no problem. But, is it standard? that's what I will be discussing in the following section.
不,这个词的经典含义没有“问题”。你可以坚持你的设计,它没有问题。但是,它是标准的吗?这就是我将在下一节讨论的内容。
what (normalization?) or is more information needed...?
什么(规范化?)或需要更多信息......?
Yes, as I implied above, there are information I can provide you with in order to standardize or "normalize" your design. Unfortunately, your purpose of this database design is not quite clear. Knowing that purpose is essential in order to sufficiently answer the question. However, I will address two possible purposes which I suppose you've got one of them.
是的,正如我上面暗示的那样,我可以为您提供信息,以便对您的设计进行标准化或“规范化”。不幸的是,您对此数据库设计的目的并不十分清楚。知道这个目的对于充分回答问题至关重要。但是,我将讨论两个可能的目的,我想你已经有了其中一个。
Purpose 1: every track can have one action at most: In this case you don't need two tables. You can have:
目的1:每个轨道最多只能有一个动作:在这种情况下,您不需要两个表。你可以有:
TrackID PK
random columns
random columns of actions /*(will stay null if no action takes place)*/
Purpose 2: every track can have zero or more actions: In this case you need two tables as the following:
目的2:每个轨道可以有零个或多个动作:在这种情况下,您需要两个表格如下:
Table 1:
TrackID PK
random columns
Table 2:
ActionID PK
random columns
CreateDate /*(search for creating a DateTime column with default to current date)*/
TrackID FK
So, if you want the latest or current action, you select top 1 with order by CreateDate desc with relation to Track.
因此,如果您需要最新或当前操作,则可以通过CreateDate desc与Track相关的顺序选择前1。
#1
The provided schema models two entities being connected by two different relations.
提供的模式模拟由两个不同关系连接的两个实体。
The first relation R1 Track - * ---- 1 - Action is modeled by the field ActionID in Table 1
第一个关系R1 Track - * ---- 1 - Action由表1中的字段ActionID建模
The second relation R2 Track - 1 ---- * - Action is modeled by the field TrackID in Table 2.
第二个关系R2 Track - 1 ---- * - Action由表2中的TrackID字段建模。
This could be a valid schema if your requirements indicate that you have two different relations.
如果您的要求表明您有两种不同的关系,那么这可能是一个有效的模式。
A simplified example of this case would include two entities Employee and Project with
这种情况的简化示例包括两个实体Employee和Project with
the relation
manages Employee - 1 ---- * - Project
管理员工 - 1 ---- * - 项目
and the relation
和关系
works Employee - * ---- 1 - Project
工作员工 - * ---- 1 - 项目
assuming that an employee works only for at most one project.
假设一名员工最多只能为一个项目工作。
The question here is do you want to model one or two relationships?
这里的问题是你想建模一两个关系吗?
If you want to model one relationship between your entities then your schema is somewhat wrong, otherwise it is right.
如果你想在你的实体之间建立一个关系模型,那么你的模式有些错误,否则就是对的。
#2
Is there a problem with the above set?
上面的设置有问题吗?
No, there is no "problem" in the classical meaning of the word. You can stick to your design and it will have no problem. But, is it standard? that's what I will be discussing in the following section.
不,这个词的经典含义没有“问题”。你可以坚持你的设计,它没有问题。但是,它是标准的吗?这就是我将在下一节讨论的内容。
what (normalization?) or is more information needed...?
什么(规范化?)或需要更多信息......?
Yes, as I implied above, there are information I can provide you with in order to standardize or "normalize" your design. Unfortunately, your purpose of this database design is not quite clear. Knowing that purpose is essential in order to sufficiently answer the question. However, I will address two possible purposes which I suppose you've got one of them.
是的,正如我上面暗示的那样,我可以为您提供信息,以便对您的设计进行标准化或“规范化”。不幸的是,您对此数据库设计的目的并不十分清楚。知道这个目的对于充分回答问题至关重要。但是,我将讨论两个可能的目的,我想你已经有了其中一个。
Purpose 1: every track can have one action at most: In this case you don't need two tables. You can have:
目的1:每个轨道最多只能有一个动作:在这种情况下,您不需要两个表。你可以有:
TrackID PK
random columns
random columns of actions /*(will stay null if no action takes place)*/
Purpose 2: every track can have zero or more actions: In this case you need two tables as the following:
目的2:每个轨道可以有零个或多个动作:在这种情况下,您需要两个表格如下:
Table 1:
TrackID PK
random columns
Table 2:
ActionID PK
random columns
CreateDate /*(search for creating a DateTime column with default to current date)*/
TrackID FK
So, if you want the latest or current action, you select top 1 with order by CreateDate desc with relation to Track.
因此,如果您需要最新或当前操作,则可以通过CreateDate desc与Track相关的顺序选择前1。