I'm designing a personal database that has a table which contains categories for seasonal events of a video game. In my design I have a related column from Events to Items (one to many [1:m] respectively). However, whenever there is no event/season going on, I'm not sure whether I should create an special row in my Events table to handle such scenarios or just leave the related column of Items empty.

I've been researching about the topic and I have several found reasonable arguments for either case (to use or not to use a null), but the problem is that none of what I've read so far is framed in use-case that would apply for my specific dilemma. As a result, I'm now even more confused as to what to do. I was originally going to add a "Non-Event" row to my table.

  • 3
    NULL is the "natural" way in SQL to store the absence of information. I personally avoid "magic values" or "magic rows" at all costs – a_horse_with_no_name Nov 6 '18 at 6:58
  • 1
    Additionally - NULL value do not interfere with foreign key reference (if it is not prohibited explicitly), so it does not affect to integrity check. – Akina Nov 6 '18 at 7:15
  • 1
    Keep in mind also that in general "blank", or empty string, is not the same as null, although sadly not all DBMSes comply. – mustaccio Nov 6 '18 at 16:34
  • @mustaccio I'm on a database course currently, and actually today I found out how nulls are ignored by the INSERT INTO statement and blanks are not. In programming I had never seen such a wild interaction change from blanks to nulls. – David Nov 7 '18 at 4:00
  • @a_horse_with_no_name Thanks, that settles it. I'm going to use nulls. Besides, what I didn't add in my question to make it simpler is that, there was a intermediate table between the ones I mentioned that would have had to "inherit" the blank category row. It seems much simpler now. – David Nov 7 '18 at 4:02

Your Answer

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

Browse other questions tagged or ask your own question.