matthewbonline — 2011-04-26T10:23:53-04:00 — #1
I am designing at MySQL Database with about 3 tables but there will be some fields that will be left blank in the tables. I am using multiple tables to limit the amount of blank cells.
The only thing I have heard about regarding this is 'overhead' and I think this means it might take longer to load the content from a table when there are blank cells in a table.
Can someone explain why it takes longer to load and what the other problems might be with having blank cells.
guido2004 — 2011-04-26T10:51:20-04:00 — #2
Why would you have blank columns? Do you mean that only in certain cases those columns would have a value, but mostly would be blank?
Can you give some examples?
matthewbonline — 2011-04-26T12:19:13-04:00 — #3
I can give a couple of examples:
In the table that defines the categories and sub-categories for a navigation bar some links have subcategories (like accessories; narrow down the search by manufacturer, product model, etc) and some have none (like products; if you click latest products it shows the latest products, no subcategories).
Another example is I intend to include accessories and products in the same table. An accessory only requires a couple of images at most. A product will feature at least 4 images. There will be a field for image names... for accessories some cells will be blank. Furthermore, I might have 3 fields for product content (text) whereas with accessories it requires less text to explain what it can do (probably use 1 of the 3 content fields).
I am most interested in understanding the problems with leaving blank cells so I can consider whether I should bother to avoid it!?
r937 — 2011-04-26T13:53:18-04:00 — #4
wherever you heard or read this, it's wrong
does that put your mind at rest?
starlion — 2011-04-26T14:17:09-04:00 — #5
Lets be a little more specific here.
Blank cells vs no column? No column is slightly quicker IF you arnt joining the table.
Blank cells vs filled cells? Filled cell will take longer to transmit unless it's a null string. (And... even then i think it might be longer because the server sends the \0)
Blank cells vs seperate table that's being JOIN'd? Rudy might correct me here, but i believe seperate-and-joined tables would be slower (since the server has to go through the compilation process)
That said, unless you're pulling massive tables or handling a huge userload, the difference in timing will be measured in microseconds at best, and generally does not bare thinking about....
wwb_99 — 2011-04-26T14:42:24-04:00 — #6
Last time I checked databases didn't have cells, they have rows and columns . . .
starlion — 2011-04-26T14:43:24-04:00 — #7
r937 — 2011-04-26T14:51:24-04:00 — #8
i'm sorry, i cannot comment further without seeing the actual tables involved
wwb_99 — 2011-04-26T15:35:11-04:00 — #9
I know that. Just funny to call them that if unless you are thinking about a database like a spreadsheet. If you are doing that then you are doomed to fail anyhow.
As for the problem at hand, I really don't think it would matter enough to be measurable unless the underlying storage is horribly designed and needs to do funny things to deal with blank columns in a row.
guido2004 — 2011-04-27T03:31:21-04:00 — #10
Ok, so what you want to do is normalize your database. You might start by googling for it and read some documentation about it.
Then try to apply it to your situation. If you need help, do come back here and ask.