In my table there will be 50 columns with float datatype, They may be empty ( ie null) , partially empty or completely full for each row.
What is the ideal design for this case
- To have float as column datatype and null for empty values
- To have float as column datatype and -1.0 for empty values
- To have varchar as datatype and null for empty values.
I have come across the fact that if columns are of variable length then the null will not occupy any space. In that case 3rd option would be desirable but am afraid of the performance due to string comparisons for search queries.
On considering performance and disk usage , which is the ideal solution for my table.
EDIT :Based on the suggestions I am dropping of the 2nd and 3rd choice.
With respect to the first choice will it be better If I create 50 seperate tables for each column and join to the main table with a primary key . Such that there won't be empty spaces and aslo I can use decimal/long as datatype. Will this solution hold good ?
Use the most appropriate datatype. If the columns are floats, then use floats. On the limited info, I would probably go with option 1. 3 would be a terrible idea.