1

Given this somewhat SQL pseudo-code:

CREATE TABLE foo(
   id      NUMBER(5) PRIMARY KEY,
   marked  BOOL
);

I'll keep track of whether ID's have been "marked" or not.

Alternatively, I've thought to alter the table to consist of a list/set, more or less:

CREATE TABLE foo(
  id NUMBER(5) PRIMARY KEY
);

such that existence in the table indicates marked = Y, whereas, the opposite notes marked = N.

In other words, to determine whether, conceptually, an id is "marked," I would check whether it simply existed in the table.

Is there any advantage to choosing one over the other? Also, I don't have experience with the latter type of table structure. Is it common?

2

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 EXISTS or NOT EXISTS operation is faster than actually retrieving the row and comparing the BOOL marked against a Y/N.

There is a noticeable difference however:

  • Approach 1 (using a marked column) has 3 possible variations of foo: 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 ModifiedDate to foo, if on your first approach.

  • Imagine that now you have another state that's "partially marked" so a 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 EXISTS / 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 UserByGroup with 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.