1

I'm trying to be realistic about normalizing a table. I need to decide to add an extra field VARCHAR(255) that will be probably empty most of the time, or make a new mapping table and store that value plus the relevant id there.

My question is, in the first approach, does that empty VARCHAR(255) field take up any space in the database?

Platform: MySQL, InnoDB

Mahdi
  • 9,247
  • 9
  • 53
  • 74
  • 1
    You will need a bit of space for the field. Even a 'dbnull' value is a stored (but small) reference. – Offler Feb 18 '13 at 08:42
  • @Offler That's not always true, however. Consider SQL Server's sparse column support - NULL values take up no space at all. –  Feb 18 '13 at 08:43
  • 1
    As for normalization: do what makes sense. I have the no NULL-FK policy, but will still allow NULL columns otherwise for "information not provided". Consider someones name: First, Last, and MI. Is it okay if they have a `null` MI? I think so. Not everyone has a middle name/initial. –  Feb 18 '13 at 08:44
  • @pst thanks for tip for normalisation! – Mahdi Feb 18 '13 at 08:46
  • @pst That is SQL Server >2008. He is asking for MySQL / InnoDB. If you use this, you will have additional informations stored for every non-null collumn ( in MYSQL / INnoDB something similiar is the COMPACT row, think about extra headers, length arguments, and then you get extra used space) – Offler Feb 18 '13 at 09:04
  • 4
    The space occupied will be so small as to be meaningless for any measure of design quality. Also, what do you mean by normalizing? A schema that is normalized to 5NF will still possibly contain NULLS. 6NF makes it possible to forbid NULL in every column. Treat normalization as a design principle, with trade offs. Don't treat it as a religious tenet. – Walter Mitty Feb 18 '13 at 13:39
  • @Offler Hence why I *explicitly said* which database. I was pointing out that it is not a universal truth: "not always true". –  Feb 18 '13 at 16:43

1 Answers1

2

Use NULL.

In SQL, null is different from the empty string ''.

The empty string specifically means that the value was set to be empty
While null means that the value was not set, or was set to null.

John Woo
  • 258,903
  • 69
  • 498
  • 492
  • 4
    @Mahdi What he says is: use varchar(length) DEFAULT NULL. This will use up minimal space when not set (approx. [2 bytes per row](http://stackoverflow.com/questions/556363/space-used-by-nulls-in-database)) – Michel Feldheim Feb 18 '13 at 08:47
  • Hey JW. would you like to edit your answer to make Michel Feldheim explanation included? That would helps a lot, I guess. – Mahdi Feb 18 '13 at 08:53
  • @MichelFeldheim: It will actually use only 1 bit per row (or a maximum 1 byte if there are no *other* nullable fields) to store that a column is `NULL`. The length of 0 is not stored when the column is `NULL`. – jeremycole Feb 28 '13 at 23:28