数据库设计问题-不同性别的不同状态

Question

场景

我想设计表格来保存牛和Calfs的数据。

牛和小牛属性/列()

• 价格
• 出生日期等(有些来自FK，如品种等)

属性保留给牛犊

• 父亲
• 母亲

属性为牛(雌性)保留

• IsMilking (Y或N)
• InseminationDate (奶牛可以多次受精，因此必须规范化，新的表应该是: ID (用牛表PK表示)和InsemDate --但应该只为雌性)。

牛有一个特殊属性 Status 具有以下特性：

• 两性中常见的：{健康，Ill}
• 只为女性保留：{怀孕，小母牛等}

由于这个不称职的设计，我的整个软件都失败了。我现时的设计如下：

现有设计

有一个表，牛参考所有的状态，品种等表，因为它是FK。

有一张小牛的桌子上有这样的栏：

ID (FK from CATTLE table) - Having cattle and calf in same table is apparently failing
FatherID (FK from CATTLE table) - Again we can have Calfs themselves as Father in this column
MotherID (FK from CATTLE table) - Same issue

提前谢谢。我已经尽了最大的努力。将表分为男性和女性(但这需要继承，我还必须拆分所有后续表，如STATUS表等)。

Answer 1

问题范围太广，而且可能有很多答案，但我试着回答，因为我对数据库设计很感兴趣。

这并不完美，但我希望它能对你有所帮助。

小牛桌(小牛记录)

​

​

与牛的关系2x

​

​

牛只表(牛只纪录)

​

​

牛的关系

​

​

人工授精表(Insem记录)

​

​

特殊属性表

​

​

特殊属性记录表

​

​

特殊属性值表

​

​

牛犊观

​

牛态视图

​

​

Answer 2

我认为为这种动物提供一张为父亲和母亲建立自我关系的桌子是最好的选择。像这样的东西；

CREATE TABLE [dbo].[Animal](
    [AnimalID] [int] IDENTITY(1,1) NOT NULL,
    [Sex] [char](1) NOT NULL CONSTRAINT [Animal_Sex] CHECK  (([Sex]='F' OR [Sex]='M')),
    [Name] [varchar](100) NOT NULL,
    [Price] [money] NULL,
    [BirthDate] [date] NULL,
    [Father_AnimalID] [int] NULL,
    [Father_Sex]  AS (CONVERT([char](1),'M')) PERSISTED,
    [Mother_AnimalID] [int] NULL,
    [Mother_Sex]  AS (CONVERT([char](1),'F')) PERSISTED,
    [IsMilking] [char](1) NULL,
    [HealthStatus] [char](1) NULL,
    [FemaleStatus] [char](1) NULL,
    CONSTRAINT [PK_Animal] PRIMARY KEY CLUSTERED ([AnimalID]),
    CONSTRAINT [AK_Animal] UNIQUE NONCLUSTERED ([AnimalID], [Sex])
)

ALTER TABLE [dbo].[Animal] ADD CONSTRAINT [FK_Animal_Animal_Father] 
    FOREIGN KEY([Father_AnimalID], [Father_Sex]) REFERENCES [dbo].[Animal] ([AnimalID], [Sex])

ALTER TABLE [dbo].[Animal] ADD CONSTRAINT [FK_Animal_Animal_Mother] 
    FOREIGN KEY([Mother_AnimalID], [Mother_Sex]) REFERENCES [dbo].[Animal] ([AnimalID], [Sex])

请注意，我添加了两个固定的计算列(Father_Sex和Mother_Sex) --这允许我为父亲和母亲创建一个更复杂的外键，强制父亲是男性，母亲是女性，并且间接地阻止父亲和母亲成为同一个动物。

Answer 3

创建一个动物表，该表包含所有人都共有的属性和状态。这将包括父级FKs，如果血统不可用，则自引用未知的动物。

然后为特定性别或阶段的属性创建单独的“扩展”表。牛，公牛，小牛等，你可以重复使用PK从动物表作为PK的扩展表，因为这是一个1:1的关系。

这也将允许动物地位与性别地位分开，这样你就可以像怀孕的牛一样拥有一个健康的动物。

我以前这么做是为了收养机构的申请。他们有一个具有共同属性的people表，但是特定于生母或孩子的属性被放在专用的表中。每个字段都有额外的30+字段。生母/孩子记录只占记录总数的25-30%，因此有必要将它们分开，而不是将60+字段添加到保证为null的主表中。