enforcing consistency among multiple 1:m relationships

Question

Given these business rules:

• Users have 0 or more accounts and all accounts are associated with a single user
• Users have 0 or more assets and all assets are associated with a single user
• An asset may be associated with a single account. If it is assigned to any account, that account must belong to the user associated with the asset.

Assume the following proposed schema:

User
-id

Account
-id
-user_id

Asset
-id
-user_id
-account_id (Nullable)

It appears there is a weakness in this schema since an asset could be assigned to an account that belongs to a different user than that asset. Is this addressed by one of the normal forms leading to a better schema? If it is not covered via normalization is the best constraint then on the business logic side?

Answer 1

The only part of this (below) that normalization might deal with is the nullable column. In Chris Date's understanding, if a column allows NULL, then the relation isn't in 1NF.

If you were trying to strictly follow the relational model, I think you'd handle this with an assertion. But most SQL platforms don't support assertions. In SQL, I believe you're looking for something along these lines. I tested this in PostgreSQL.

create table users (
  user_id integer primary key
);

create table accounts (
  user_id integer not null references users (user_id),
  account_id integer not null unique,
  primary key (user_id, account_id)
);

create table assets (
  user_id integer not null references users (user_id),
  asset_id integer not null unique,
  account_id integer null,
  primary key (user_id, asset_id),
  foreign key (user_id, account_id) references accounts (user_id, account_id)
 );

-- Insert 3 users.
insert into users values (1), (2), (3);

-- User 1 has two accounts, user 2 has 3 accounts, user 3 has none.
insert into accounts values 
(1, 100),
(1, 101),
(2, 102),
(2, 103),
(2, 104);

-- User 1 has 1 asset not assocated with an account.
insert into assets values (1, 200, null);

-- User 1 has 1 asset associated with account 101
insert into assets values (1, 201, 101);

-- User 1 tries to associate an asset with account 102, which doesn't belong to user 1.
insert into assets values (1, 202, 102);
[Fails with foreign key violation]

-- User 2 has two assets not associated with an account.
insert into assets values
(2, 500, null),
(2, 501, null);