3

Is it bad to have a column empty as NULL often in a table?

comment table

comment_id     member_id    user_id
1              1            NULL
2              1            NULL
3              1            NULL
4              1            NULL
5              1            NULL
6              1            NULL
7              NULL         1
8              NULL         1
9              NULL         1
10             1            NULL
Run
  • 54,938
  • 169
  • 450
  • 748

3 Answers3

4

No, it's not inherently bad. A NULL entry is a tool, and you may use that tool as you like.

Now, in the case you posted, you might consider having non-overlapping user and member IDs and using one column to store either, but that's your decision.

Borealid
  • 95,191
  • 9
  • 106
  • 122
  • Thanks for the answer Borealid! :-) – Run Feb 21 '12 at 17:38
  • `Now, in the case you posted, you might consider having non-overlapping user and member IDs and using one column to store either, but that's your decision.` sorry I dont quite get this - do you mean one table for users and another table for members? – Run Feb 21 '12 at 17:39
  • 2
    @lauthiamkok I meant that, if someone is either a "member" or a "user", you could assign a different type of ID to each one (so you could distinguish which type they were from the ID). Then you wouldn't need two columns. But, like I said, up to you. – Borealid Feb 21 '12 at 17:40
  • `a different type of ID` - like `member-1` for a member and `user-2` for an user? – Run Feb 21 '12 at 17:50
  • @lauthiamkok That would work, yes. The idea is just to let you do a join without having to worry about colliding IDs, were you to have separate tables for each of the user types. – Borealid Feb 21 '12 at 17:56
  • oh then what should I name the column that colliding two columns (like the ones I have - user_id and member_id) together then? – Run Feb 21 '12 at 18:02
  • Just noticing: `'member-1'` and `'user-2'` is not the best. Perhaps two columns `'M', 1` and `'U',2` instead. – ypercubeᵀᴹ Feb 21 '12 at 23:21
2

It's probably not causing much of a performance issue. It looks like you have some sort of distinction between members and users in your table. Assuming nobody can be a member AND a user, you could have one column for member/user id but then you would need another column to identify whether they were a user or a member. That would actually require MORE storage than the solution you have now so I think you're fine.

Matt Dodge
  • 10,833
  • 7
  • 38
  • 58
  • It would actually require the same storage or less. Instead of two ids (lets say 4+4=8 bytes), you could have one id and the distinction column (that's 4+1=5 bytes). – ypercubeᵀᴹ Feb 21 '12 at 23:27
  • NULL values should take 0 bytes though, no? I do not think MySQL will still allocate 4 bytes if the values are null, I could be mistaken though – Matt Dodge Feb 21 '12 at 23:57
1

No, it's not 'bad' although some die-hard Normalization fanatics will insist that allowing nulls violates relational database laws.

If you have a table with a lot of them, you might want to take a look and see if the design is appropriate but you can't say it's bad in every case without more context.

Widor
  • 13,003
  • 7
  • 42
  • 64
  • It doesn't matter if we insist or not. It does violate relational theory. It's not like the end of world but it has several drawbacks. – ypercubeᵀᴹ Feb 21 '12 at 23:24