When designing a database, how to keep track of nonexistent entities?

Question

Sorry if the title is a bit confusing, but I don't really know how to convey the question in a straightforward way.

This is a design problem. Let's say you have this really simple tables

create table people (
   id_person serial primary key,
   name text null
)

create table dogs (
   id_pet serial primary key,
   id_person int null, 
   name text null,
   CONSTRAINT dogs_people_id_person_fk FOREIGN KEY (id_person) REFERENCES people (id_person)
)

insert into people (name)
values ('Jerry'), ('Jenny'), ('Alan')

insert into dogs (id_person, name)
values (1, 'Felix'), (2, 'Pippo'), (2, 'Lampino')

So you list the people and then all the dogs they have; they can have 0 or many.

My question is: how can I say that Alan doesn't have a dog? Well, I can make a query and find out that there is no dog listed in dogs for Alan. But I want to keep track of this information!

Let's say for example these data are obtained by interviewing people in a classroom: I don't want to have any doubt if Alan doesn't have a dog or if I haven't interviewed him yet.

So in a database design standpoint what is the more correct way to make it?

Should I add a new column in people (like, has_no_dogs)? But people is about people, seems a little forced to add an information about dogs in the people table, doesn't it?

Should I add a new table? Something like dogless_people where I list the people I know that don't have a dog. Or is redundant?

What do you suggest?

--EDIT To add a little for my specific case: people is a big table, central in a relational net, whereas dogs is very specific table used for just one topic. So I think it would be a bit "dirty" to add a column like has_no_dogs, or interviewed, in the people table.

Answer 1

The most proper way to tell that Alan has or does not have a dog is to just write a query.

select count(*) from dogs where id_person=(select id_person from people where name='Alan`)

To find who does not have any dogs you can use a query

select name from people where id_person not in (select id_person from dogs)

If you want to always have number of dogs in the people table, you can go by way of trigger:

create table people (
   id_person serial primary key,
   name text null,
   count_of_dogs integer not null default 0
)

create trigger add_dogs after insert on dogs for each row
begin
  update people set count_of_dogs = (select count(*) from dogs where NEW.id_person)
end;

create trigger remove_dogs after delete on dogs for each row
begin
  update people set count_of_dogs = (select count(*) from dogs where OLD.id_person)
end;

As another variant, you can just define a view:

create view people_with_pets as
select
   id_person,
   name,
   (select count(*) from dogs where dogs.id_person=people.id_person) as count_of_dogs

Depending on DBMS, the view can be a dynamic and be calculated each time. Or materialized (cached) and be updated only on demand or even automatically on changes in dogs. For example, in Oracle you can control how often to recalculate the view, while SQL Server effectively creates a set of triggers on each subtable in the view and updates it immediately.

Each variant has its own limitations and advantages. And can have different sets of pros and cons depending on DBMS.

Answer 2

To know whether a person really has no dogs or just hasn't yet been interviewed add a column to people. Call it something like "last_interviewed". If it is NULL for Alan he's not been interviewed yet and that's why there are no dogs for his ID. If it is populated then he really has no dogs.

Answer 3

My question is: how can I say that Alan doesn't have a dog?

You query the dogs table for any rows tagged with Alan's id.
If none are found, then Alan doesn't have a dog.

But I want to keep track of this information!

But you are keeping track of it.

Should I add a new column in people (like, has_no_dogs) ...

Absolutely not.

... seems a little forced to add an information about dogs in the people table

Absolutely, yes.

Do not store data that you can derive ("Age" from "Date of-Birth" being the classic example).
Someone having a dog can be found by querying the dogs table. As soon as you add an extra "flag", you run the risk of the flag being "out of step" with the Truth (in the dogs table).

However ...
If "dogs" were something else and there might be thousands of them for each person, then you might consider the optimisation of holding such a flag, maintained by database triggers, to prevent the need to do this "extra" lookup each time.

... these data are obtained by interviewing people in a classroom: I don't want to have any doubt if Alan doesn't have a dog or if I haven't interviewed him yet.

Trust your Data.
Once it's in your database, it is your Single Source of Truth.
That means storing it correctly and accurately. (You are storing all [or nothing] of this as part of a Transaction, aren't you?)

If you have a person record for Alan then you've interviewed him and if, as a result of that, there are no entries in the dogs table for him, then he has no dogs.