1

Say I have a table called listing and it has the following columns:

id | name   | description | price | created_date|
 0 | iPhone | A phone     | 1000  |   7/4/2020  |

Since I want to track when a listing is sold, I'm considering adding a sold_date column.

My concern is that this would cause a bunch of rows in the listing table to have a null in the sold_date column since every listing wouldn't be considered sold. Would having null values be okay in this case, or should I create another table that would contain the sold_date column and references a listing?

Improve this question
1
  • From a theoretical point adding a separate table wpuld be prefered, but either way you need to decide what to do with historical data.
    – Lennart - Slava Ukraini
    Commented Jul 5, 2020 at 8:22

1 Answer 1

Reset to default
1

There is a normal form that would separate this column into its own entity (table). If you want to go down that path it's a legitimate choice. In my experience it's rare, though.

Accounting for unknown values is part of life and business computing. Procedural languages have their techniques. SQL has nulls. One way or another you'll eventually have to deal with nulls in the system, even if all table columns are defined "NOT NULL". Outer joins, for example, may introduce them.

As for performance, RDBM systems have efficient mechanisms to deal with nulls so this should not be a concern.

Were I writing this I would add the nullable column to the existing table.

Improve this answer

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