Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

about null fields in a table

 
Roldan Baldo
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i have a table for example,

Address
userId | homeAddress | currentAddress

normally a table will look like this, and im worried when my users will vastly increase, some will not fill out the home address or the
current address, so there will be a field that is null in the table if that will be the structure.

so I re design my database, like this, to avoid some null fields.

Home_Address
userId | homeAddress

Current_Address
userId | currentAddress

so i have now 2 tables. Does Null fields matter? Does my second database design fit what i wanted? does that advisable?
because im very particular about null fields. I dont want any null fields in my tables.

any comments/suggestions would be a great help. thank you.

regards

bhadz
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do you intend to create separate tables for each field ? What if you have ten other fields ? You'd create ten other tables ?

I don't see any problem having null fields in a table like yours.
 
Roldan Baldo
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
if I see that it would be sometimes null, I will separate it. would it be okay?

thanks.

regards

bhadz
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Separate it ? What do you mean ?
 
Roldan Baldo
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
separate it to other table, i will make another table for it. Just to prevent null fields.
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why do you want to separate it ? You didn't answer my first question. Would you make dozens of small tables just to avoid having nulls ?

Why are you so concerned with null fields ? Your first table (userId | homeAddress | currentAddress ) is fine.
 
Roldan Baldo
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i will separate it because i think that null fields will consume bytes in the database. and if my data will increase and having many null fields
will have an effect of consuming spaces in my database.

yes i will most likely want to have many tables just to avoid null fields. but im confuse if my style would be alright. thats why im getting
your opinions.

thank you for the reply i appreciate it.
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you want my opinion : don't separate them. Imagine one second what would happen if every applications had to separate each and every field in separate tables to avoid storing nulls. Nulls take (virtually) no space.
 
Roldan Baldo
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you very much for the explaination. Now I get it. i thought nulls takes space.
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It may depend on databases, but as far as Oracle is concerned :

To conserve space, a null in a column only stores the column length (zero). Oracle Database does not store data for the null column. Also, for trailing null columns, Oracle Database does not even store the column length.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic