If you just need to know if an
ID is marked or not, using your 2nd approach (not registering the ones not marked or marked) has a few more benefits than the first: less rows saved means more unused space, and a
NOT EXISTS operation is faster than actually retrieving the row and comparing the
BOOL marked against a
There is a noticeable difference however:
- Approach 1 (using a
marked column) has 3 possible variations of
foo exists and is marked,
foo exists and it's not marked or foo doesn't exist.
- Approach 2 (just using
ID) has only 2 variations: either
foo exists which we assume it's marked, or it doesn't which we can assume that it's either not marked or it doesn't exist. This brings a problem of discerning these last two scenarios.
On another hand, if you are going to need additional information for
foo (like most systems) you will have to add more columns to it.
Imagine that now you need to know the date when it was marked or
unmarked. You can track this with another table, or just add a
foo, if on your first approach.
Imagine that now you have another state that's "partially marked" so
BOOL's 2 possible values (other than NULL) is not enough. You will now need to change it to a
VARCHAR, or an
INT with a foreign key to another table that explains what that status is. If you had implemented the 2nd approach, you will have to change a big part of your queries (removing the
NOT EXISTS), rather than just a few literals or hard-coded numbers, which adds additional maintenance time.
Another thing to point out is the relationship of this
foo to other entities. If another entity might relate to 1
foo (no more than 1) or not, then it would probably be part of that other entity's table, but on the other hand, if an instance of another entity can relate to
foo in a
1 - N relationship, then the foreign key must be on
foo, which you won't be able to add if you are using the 2nd approach, since an unmarked
foo has no record.
From your question title I can guess that this
foo represents the link between a
User and a
Group. If that were the case, the common approach would be using a
User table with
UserID as PK, a
Group table with
GroupID as PK and a many to many breakdown table
UserID, GroupID as composite PK, each being a foreign key to the related table. You could store the
CreatedDate in this table if you need additional info, for example.