Does a record with NULL data does not eat any storage on the harddrive

database-design

This is not an expert question about database, so I'm not sure if this question can be posted here or not. So if this question is out of place, feel free to close it.

It's a long development story, but long story short, the managers want to create a generic table to store various assets data. They want the table to be able to hold various type of data, eg. they want the table to be able to hold a car's number of tires, car's color, car's year model, etc, but they also want the table to be able to hold a house's address, floor size, etc.

So they want to create this generic table for record all the various assets' details with like 20 VARCHAR(45) columns, 20 DOUBLE columns, and 20 BOOLEAN columns. I have been against this decision because I'm afraid that the unused column on the row will used up space in harddrive. But they said that it would not used up space in harddrive if the record is NULL. So if for 60 columns, I only use 3 column in a row of data, then the 57 other columns will not take space because they are NULL. Is this true? Which one is true? Thanks.

Best Answer

You haven't mentioned your RDBMS system. You should always do this when asking any question.

Secondly, this would be easy to test. I'm sure that NULLs will add storage - maybe your question is about "How much storage exactly?"?

Why not add 10,000 records of your structure with full data, then with NULL data and compare the size on disk. I'm fairly sure that the exact number will vary by RDBMS. There will be some overhead associated with fields even if empty.

There are many reasons why managers decide to do things - one of them is a lack of understanding of the technology. One solution might be to have another table "hanging" off your main table - only those records for which extra data is available will be in your subsidiary table?

To answer your last comment

"i have also looking at another solution, like having a table with a 
few columns, with each row is storing one property key with its value on 
the next column. But this ended up pushes the development back too far 
because of the complexity in data design and communication."

This sounds like you are considering an EAV-type design - NOT a good idea - see here and links from that post.

Related Solutions

Proper use of NULL, and utilizing CHECK constraints for business logic vs stored procedures

Querying a column that can contain nulls is more complex than querying a column that cannot. So also is querying multiple tables more complex than querying one table. I wouldn't let the avoidance of null drive the normalization.

For example, you mentioned that all devices will eventually be disposed and get a disposal date. If there are no other columns in the Disposal table, then in my mind it makes more sense to put DisposalDate in the Device table. Other tables like Sanitize might make more sense as separate tables because there are multiple data points that will not apply to some Devices.

Check constraints are great and should be used when possible, but there will always be times when a procedure is necessary.

Database Design – How to Handle Conditional Fields

Your approach is reasonable. Put a check constraint on the ASSET table to ensure exactly one of your foreign keys is ever set at any point in time.

The flag is redundant since the same information is captured by which of the foreign keys is NULL. Keeping it just adds maintenance overhead to the application. Eventually it will become inconsistent and then you'll have a mess to sort out.

An alternative design is to combine some of the information from SHOP and JOB into a LOCATION table. The schema then becomes

ASSET(ASSET_ID, LOCATION_ID)
LOCATION(LOCATION_ID, NAME)
JOB(JOB_ID, LOCATION_ID)
SHOP(SHOP_ID, LOCATION_ID)

Put one row in LOCATION for each "job site or .. warehouse or vendor facility," as you say in your question.

Best Answer

Related Solutions

Proper use of NULL, and utilizing CHECK constraints for business logic vs stored procedures

Database Design – How to Handle Conditional Fields

Related Question