"a boolean column stating whether or not a group has any users"
Use EXISTS:
CREATE VIEW group_info AS
SELECT g.name, NOT EXISTS (SELECT 1 FROM users u WHERE u.group = g.id) AS empty
FROM groups g;
This returns 1 row per group, no matter whether there are users or not - not one row per user like you had, but probably didn't want - so we don't need DISTINCT.
It's largely irrelevant whether you write SELECT 1 or SELECT * or SELECT 'foo' in the EXISTS subquery. Only the bare existence of at least one row matters.
With number of users
CREATE VIEW group_info AS
SELECT g.name, count(u.group) AS ct_users
FROM groups g
LEFT JOIN users u ON u.group = g.id
GROUP BY g.name;
LEFT JOIN is crucial to retain groups without users.
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.
Best Answer
From what I can tell from your image (please post ddl instead), there is no relationship between adminstrateur and technicien_sav. If you add an attribute created_by or administered_by in technicien_sav, there would be a relationship. Since this attribute most likely is not part of the key for tech... , it would be referred to as a non-identifying relationship. Example:
I prefer using the same attribute name throughout the model, names like id, name, etc are IMO to vague. Even better is to use names from the domain of discourse, but I have no idea what the might be in your case.