Create database design with requirement

Question

I has a small database structure with an Account and Person. I want apply the following requirement:

Account have one Person

Person do not have necessarily one Account (can be null)

When I delete an Account I don't necessarily delete Person

When I delete a Person I want delete Account

How to do that ? (one to one relationship ? on delete cascade ?)

This is good ?

Some clarification after getting some answers:

Thank for your answers. I try to explain more the contextual meaning of Account and Person.

A Person is a contact with personal profile (Physical person, Customer, Company).

An Account is specified by an username, password, email. It's really an account for sign in on a app.

Therefore a Person can be exist without a account. A Person can only have a maximum of 1 Account. In a other direction Account have necessarily a Person.

Consequently when we remove an Account the Person is not also remove. But When I remove Person this associated Account no longer needed.

PS: I use PostgreSQL.

Answer 1

I would state it like this:

• Each Person may be the holder of one or more Accounts.
• Each Account must be held by one and only one Person.

Given that, let's address a few of the questions and statements posed:

Person do not have necessarily one Account (can be null)

The Account would not be null. Instead, there just wouldn't be a row in Account for that Person as they hold no accounts. Now if you write an outer join of Person to Account then yes, for those persons with no accounts the columns from the account, which is the null supplying side, will be null.

When I delete an Account I don't necessarily delete Person

When I delete a Person I want delete Account

How to do that ? (one to one relationship ? on delete cascade ?)

You are right on with a one to many relationship from Person to Account, not a one to one relationship. You are also correct you want deletes of a person to cascade to ensure that when a person is deleted the associated accounts are deleted with it.

In your drawing you have, under the Account table:

Constraints - person_id is unique

and under the Person table:

Constraints - person_id is DELETE_CASCADE, UPDATE_CASCADE

Both of these are not technically correct. Regarding person_id on the Account table, since the relationship is one person to many accounts, the same person_id will be in the account table for each account that person holds. It would look like this:

account_id     person_id
----------     ---------
1              1
2              1
3              1

The account_id is unique, not the person_id.

Regarding person_id as a constraint under the Person table, actually the foreign key constraint is placed on the Account table, referencing the Person table. That constraint is then declared to cascade on delete and update actions. The constraint on the person_id in the Person table would be a primary key constraint.

Here is a revised drawing of the relationships using Oracle SQL Developer Data Modeler:

The oracle tool places an X over the crows foot on the many side to show that the delete rule is CASCADE. It also shows the keys, which show the foreign key constraint with person_id on the Account table, and the primary key constraint with person_id on the Person table.

Answer 2

An Account belongsTo a Person. A person can exist without an account.

If a Person hasOne Account, then Account's primary key should also be a foreign key to People.

create table people (
  person_id int primary key,
  meta1 text, 
  meta2 text
);

create table accounts (
  person_id int primary key references people(person_id) on delete cascade,
  meta1 text
);

Answer 3

Looks good if you want a Person to have 0 or many Accounts.

If a Person can only have a maximum of 1 Account, then I'd put the account details in the Person table.