Database design - nullable fields

Question

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

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.

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

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';

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?!)

Thanks

Effectively, this is the same as this question : possible duplicate of [Creating database table](http://stackoverflow.com/questions/12035131/creating-database-table) — podiluska, Aug 21 '12 at 08:45
fair enough, but that question doesn't have a very definitive answer! — DaveB, Aug 21 '12 at 09:05
go for it...I cant see how the answer to that linked question removes the need for nullable fields, could you enlighten me? easy points! — DaveB, Aug 21 '12 at 09:20

score 4 · Accepted Answer · answered Aug 21 '12 at 09:27

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)

Thanks podiluska, good answer, you have convinced me to take the normalised route...(+1 for using an s instead of z) — DaveB, Aug 21 '12 at 09:52
The "problem" with this solution is control that differents users don't share the same notification. — doctore, Aug 21 '12 at 10:51
@podiluska How can you prevent that different users (or user types) share the same notification? — doctore, Aug 21 '12 at 10:59

Database design - nullable fields

1 Answers1

Linked