在SQL Server中将五个表与一个表之间的关系设置为不拒绝数据库规范化

I have some tables with names Contacts, Follows, Tasks, Tickets, Accounts and ... Each of the above tables, should have relation with Notifications table.

我有一些名称为Contacts,Follows,Tasks,Tickets,Accounts和...的表格上面的每一个表都应该与Notifications表有关系。

Now I use this structure for Notifications table in SQL to create relation between them:

现在我在SQL中使用此结构用于Notifications表来创建它们之间的关系:

Id, ContactId, FollowId, TaskId, TicketId, AccountId, Text

Id is the primary key and ContactId, FollowId, TaskId, TicketId, AccountId should be foreign keys.

Id是主键,ContactId,FollowId,TaskId,TicketId,AccountId应该是外键。

Every time a record is added to this table, just one column of this foreign keys list got value, like this:

每次将记录添加到此表时,只有此外键列表的一列有值,如下所示:

Id  ContactId   FollowId    TaskId  TicketId    AccountId   Text
1   null        null        null    null        2           notification test 1
2   null        null        null    12          null        notification test 2
3   null        null        null    11          null        notification test 3
4   5           null        null    null        null        notification test 4
5   null        1           null    null        null        notification test 5
6   null        null        null    null        3           notification test 6
7   null        null        null    null        43          notification test 7

Is this a fine architect that does not refuse Normalization in databBase?

这是一个很好的架构师,不会拒绝databBase中的规范化吗?

2 个解决方案

#1

Structure you created is denormalized structure not ideal for OLTP environment.

您创建的结构是非规范化结构,不适合OLTP环境。

Below would be ideal normalized structure

下面是理想的标准化结构

Notifications Contacts
Notifications Follows
Notifications Tasks
Notifications Tickets
Notifications Accounts

#2

Is it a fine solution? Well, not really. But SQL doesn't have a great solution for entities (such as notations) that are related to a bunch of other entities.

这是一个好的解决方案吗?嗯,不是真的。但是对于与一堆其他实体相关的实体(例如符号),SQL没有很好的解决方案。

This particular solution has the advantage that you can declare foreign key relationships. It has the disadvantage that adding new entity types requires restructuring the table and that each of the NULL values occupies space. So, it is not particularly scalable.

此特定解决方案的优点是您可以声明外键关系。它的缺点是添加新的实体类型需要重组表,并且每个NULL值占用空间。因此,它不具有特别的可扩展性。

There are no perfect solutions. If you are willing to give up the foreign key relationships, you can just do:

没有完美的解决方案。如果您愿意放弃外键关系,您可以这样做:

create table notifications (
    notificationId int identity(1, 1) primary key,
    notificationType varchar(32),
    relatedId int,
    check notificationType in ('contact', . . .)
);

Unfortunately, SQL Server does not have filtered foreign key relationships. You can do something like this:

不幸的是,SQL Server没有过滤外键关系。你可以这样做:

create table contacts (
    contactId int identity(1, 1) primary key,
    . . .
);

create table n (
      id int identity(1, 1),
      relatedId int,
      notificationType varchar(32),
      contactId as (case when type = 'contact' then relatedId end) persisted,
      foreign key (contactId) references contacts(contactId)
);

This almost does what you want. The problem is that contactId needs to be persisted to be used in a foreign key relationship -- and persisted computed columns still occupy space.

这几乎可以满足您的需求。问题是需要持久化contactId以用于外键关系 - 并且持久化的计算列仍然占用空间。

#1