Are Two Nullable Integer Database Fields More Space-Efficient Than One Not Null Integer Field and One Not Null VarChar(1) Field?

Question

I don't know a lot about efficient database structures. I'm wondering which of the below structures would take up less space long-term.

Table 1

This table has two integer columns post_id and note_id that are allowed to be null. There is always one integer filled in for either post_id or note_id. Meaning, if post_id has a value, note_id will be null and vice-versa.

+--+-----+-------+-------+-------+
|id|value|post_id|note_id|user_id|
+--+-----+-------+-------+-------+
|71|-1   |51     |<null> |1      |
|77|1    |<null> |1      |2      |
|23|-1   |<null> |2      |1      |
|24|1    |<null> |3      |1      |
|25|-1   |52     |<null> |4      |
|79|1    |<null> |19     |1      |
|84|1    |<null> |19     |3      |
|62|1    |23     |<null> |3      |
|90|1    |43     |<null> |1      |
|91|1    |72     |<null> |3      |
|41|1    |<null> |19     |4      |
+--+-----+-------+-------+-------+

Table 2

This table merges post_id and note_id to be a single integer column object_id not null with a new varchar column that takes only one letter (n for note, p for post). object_id would never be null and type would never be null.

+--+-----+---------+----+-------+
|id|value|object_id|type|user_id|
+--+-----+---------+----+-------+
|71|-1   |51       |p   |1      |
|77|1    |1        |n   |2      |
|23|-1   |2        |n   |1      |
|24|1    |3        |n   |1      |
|25|-1   |52       |p   |4      |
|79|1    |19       |n   |1      |
|84|1    |19       |n   |3      |
|62|1    |23       |p   |3      |
|90|1    |43       |p   |1      |
|91|1    |72       |p   |3      |
|41|1    |19       |n   |4      |
+--+-----+---------+----+-------+

Which approach saves the most space and why?

This answer says:

For fixed width fields like nullable int the storage space required is always the same regardless of whether the value is null or not.

For variable width nullable fields the value ’NULL‘ takes zero bytes of storage space (ignoring the bit to store whether the value is null or not).

I don't know what that means. Fixed width or variable width fields? Do cells always take up space if on an integer field?

I'm using Django and my project is too deep to switch to a GenericForeignKey. I'm thinking one of the above two table schemes is best and most simplistic.

question from:https://stackoverflow.com/questions/65857825/are-two-nullable-integer-database-fields-more-space-efficient-than-one-not-null

Answer 1

Waitting for answers