1

I have a table myTable having a foreign key, let's say it's called credential_id. Most of the time an authenticated user uses it by doing some actions thus credential_id points to some value (when a row is being inserted into myTable, credential_id has a value).

However, I want to allow anonymous (not authenticated or guests) users also do some actions involving this table. Since they are not authenticated, credential_id can't have any value.

I wonder, what would be better: to allow null values for credential_id or create a special account for an anonymous user and use it whenever the user is not authenticated? The question is about so-called null pattern in a way, but regarding a database.

Improve this question
3
  • I think the special account way better, because it explicitly say what you're trying to do. Commented Feb 22, 2014 at 0:02
  • If you have any related data that you either store in let's say credentials table or hard code for anonymous users then it makes sense to store it. Until you don't have any values/data like that - basically you do not do anything else with anonymous users it's not worth having an extra entry and values pointing at it. However the first scenario is more likely Commented Feb 22, 2014 at 0:04
  • Incidentally, questions along these lines, relating to database design, may be addressed in more detail on dba.stackexchange.com. Commented Feb 22, 2014 at 0:23

1 Answer 1

Reset to default
3

Such records are commonly used in data warehouses, because NULL values are usually ambiguous. Creating dummy records for various unusual circumstances is preferred, to eliminate this ambiguity. In your case, some kind of "guest" credential would be perfectly appropriate.

That said, if you expect this to be the only reason for an action to not have a credential, using NULL values is fine, too. It's reasonably intuitive: this action doesn't have any creds, but did occur, I guess the user didn't need to authenticate.

You'll need to weigh the (modest) cost of implementing a guest credential versus the chance of confusion from using NULL values.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.