0

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.

1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White
    Mar 6, 2016 at 23:46

2 Answers 2

3

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.

0
3

The problem with this design is not the amount of storage it will use, but that it violates most of the principles of database normalization.

What does a row in this table even represent? A car? A house? Other objects? What does it mean for a row to have values in columns that are inappropriate for its type (e.g, a house with a number of tires)? Why are these values even stored together? What happens if a new type of entity needs to be added to this table? (Keep in mind that the time taken to ALTER TABLE grows with the overall size of the table.) What happens when you reach the maximum number of columns enforced by your database?

In summary, the database structure you're proposing here is horrible. Depending on your needs, one of these alternatives may be more appropriate:

  1. Creating a separate table for each distinct type of entity that needs to be tracked.

  2. Creating a single table with a minimal number of columns to store properties common to all entities, then storing additional properties in a secondary table specific to that type of entity.

  3. As in #2, but storing those additional properties in a single key-value store (i.e, with one row per property for each entity).

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.