数据库设计-可为空的字段 [英] Database design - nullable fields

问题描述

我是数据库设计的新手，这是一个最佳实践"问题，我想确保自己在正确的方向上学习

Bit of a 'best practice' question as I am new to DB design and I wanted to make sure I am on the right tracks with this one

我有3种用户类型，用户(单人)，组(大量用户)和公司(大量组)，每种用户都有自己的登录名，允许他们发布消息.因此，例如.如果公司发布了一条消息，它将显示在所有链接的用户新闻提要中.

I have 3 user types, user (single person), group (lots of users) and company (lots of groups), each has their own login which allows them to post messages. So eg. if a company posts a message it will appear in all the linked users news feeds.

为此，我有一个表"messages"，用于存储消息内容以及用于链接用户类型的外键

To achieve this I have a table 'messages' that stores the message contents, along with the foreign keys to link the user types

我打算使用以下架构(PostgreSQL)来实现此目标...

I was going to use the following schema (PostgreSQL) to achieve this...

create table notifications(
    notification_id serial primary key,
    user_id integer references users,
    group_id integer references groups,
    company_id integer references companies,
    date_created timestamp not null default now(),
    title_id text not null,
    message_id text not null,
    icon text not null default 'logo'
);
comment on table notifications is 'Messages to be displayed on a users home feed';

这将允许我构造一个查询，以提取与用户新闻提要相关的消息(例如，只有一个字段user_id，group_id或company_id将具有值)

This would allow me to construct a query that pulls out the relevant messages for a users news feed (eg. only one field user_id, group_id or company_id will have a value)

但这是最好的方法吗?我肯定拥有可空的外键是一个坏主意，我想使用枚举键可能会有更好的解决方案?(这甚至存在吗?！)

But is this the best method? I am sure that having nullable foreign keys is a bad idea, I was thinking there might be a better solution using a kind of enumerate key? (Does this even exist?!)

谢谢

推荐答案

高度标准化的一种选择是使表更像

One option, highly normalised is to make the tables more like

create table notifications( 
    notification_id serial primary key, 
    date_created timestamp not null default now(), 
    title_id text not null, 
    message_id text not null, 
    icon text not null default 'logo' 
); 

create table usernotifications
(
    notification_id integer references notifications,
    user_id integer references users
);

create table groupnotifications
(
    notification_id integer references notifications,
    group_id integer references groups
);

create table companynotifications
(
    notification_id integer references notifications,
    company_id integer references companies
);

其中条目仅存在于任何给定通知的相关(用户/公司/组)通知表中.

where entries only exist in the relevant (user/company/group)notifications table for any given notification.

(我认为在可以表明外键是可选的情况下，可为空的外键没有任何问题，但是类似类型的多个外键确实给人以非规范化设计的印象)

(I don't think there is anything wrong with nullable foreign keys in the situation where that indicates that the foreign key is optional, but multiple foreign keys of similar type does give the impression of a denormalised design)

这篇关于数据库设计-可为空的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！