SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2011
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Empty cells within tables within database

    Hi,

    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.

    Kind regards,

    Matt.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,499
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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?

  3. #3
    SitePoint Evangelist
    Join Date
    Mar 2011
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!?

    Matt.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by MatthewBOnline View Post
    I am using multiple tables to limit the amount of blank cells.
    wha ??

    Quote Originally Posted by MatthewBOnline View Post
    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.
    wherever you heard or read this, it's wrong

    does that put your mind at rest?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MatthewBOnline View Post
    Hi,
    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.
    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....

  6. #6
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,633
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Last time I checked databases didn't have cells, they have rows and columns . . .

  7. #7
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    cell = row x column.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i'm sorry, i cannot comment further without seeing the actual tables involved
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,633
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    cell = row x column.
    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.

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,499
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by MatthewBOnline View Post
    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!?

    Matt.
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •