locked
A table with Multiple columns VS Multiple Tables RRS feed

  • Question

  • I'm trying re-design existing system wherein, some of the tables in the the existing database has close to 300 columns. Most of these columns are updated every day. In the new system I'm thinking of physically splitting the fields to multiple tables but I'm not sure if this approach is good? Can you tell me under what circumstances can I follow this approach and under what situations I shouldn't?
    Friday, May 13, 2011 12:54 AM

Answers

All replies

  • This all comes down to Database Normalization (http://en.wikipedia.org/wiki/Database_normalization). The level of normalization differs from database to database and depends on the data you are storing and how you want to retrieve it.

    The following link contains a tutorial giving an insight into the normalization process http://www.phlonx.com/resources/nf3/

    You would need to provide a copy of your table schema for anyone to assist you.  A brief explanation of how the data is used would also be beneficial.


    Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
    Friday, May 13, 2011 1:30 AM
  • I agree with Jeff that it all comes down to database normalization.

    But You should understand the application too and what sort of queries it will fire.

    A row will be saved in 8 kb page. If multiple rows fit in a single page, that much the better. Your IO will be much less in such case.

    you can  break down the table functionally, and have a one to one relation with them

    In such cases whenever a query fires, it will pick up the functionally relevant tables only.

    SAy you have a customer table with his personal details, Preferences and all other odd information.

    The Preferences and all other odd information will be required some times only and not always.

    The customer personal details will be required always.

     

    Hence more rows can be fitted in customer main table


    Friday, May 13, 2011 4:29 AM
  • There are two general types of databases one is the OLTP and the OLAP database

    or basically a normalized database and a datawarehouse.

    The normalized database (the one you are thinking) is designed to capture Online transactions such as

    inventory, POS, timesheet. etc.  This type of database is ideal for many insert.

    the downside of a normalized database is that it is "very slow for reporting" that's why

    we have to use ETL to make our Datawarehouse.

    While normalized database talk about entities datawarehouse talks about "fact tables" and "dimensions"

    A fact table is usally a combination of many entities thats why it may reach as many as 300 columns.

    Fact tables are ideal for report purposes because the content of the datat is already processed for that report purpose.

    Fact tables eliminate the needs of doing "join querries in the normalized DB"  

     

    Your database design will have to vary on your needs. if it is for OLTP purpose the use normalized. if its is for reporting do the

    datawarehouse design

     

     

     

     

     

     

     

     

     

     


    http://joeydj.com
    Saturday, May 14, 2011 6:00 AM
  • Your database design will have to vary on your needs. if it is for OLTP purpose the use normalized. if its is for reporting do the datawarehouse design


    http://joeydj.com

    It's not as clear cut as that.  Many databases support both data collection and reporting.  There's nothing wrong with implementing a de-normalized table in an OLTP database to support reporting.


    Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
    Saturday, May 14, 2011 6:04 AM
  • There is something wrong with that. If I have a "Mission critical" database such as one used by ATMs(automated teller machine) or One that is used in the "stock market" by more than 2M people all at the same time, that transaction database cannot accomodate reporting.  for department purposes your idea might work. but for global business such as call center data which takes in 3 million records per hour there is no way for that database to do reporting

    thats why i recommend that reporting database should be placed away from OLTP databases.

     


    http://joeydj.com
    Wednesday, May 18, 2011 4:17 PM
  • There is something wrong with that. If I have a "Mission critical" database such as one used by ATMs(automated teller machine) or One that is used in the "stock market" by more than 2M people all at the same time, that transaction database cannot accomodate reporting.  for department purposes your idea might work. but for global business such as call center data which takes in 3 million records per hour there is no way for that database to do reporting

    thats why i recommend that reporting database should be placed away from OLTP databases.

     


    http://joeydj.com

    In this scenario you are correct.

    I didn't say every database should support reporting, what I said was:

    "It's not as clear cut as that. Many databases support both data collection and reporting."

    Stating that an OLTP database should not be used for reporting is not correct as it all depends on the system that your database is linked to


    Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
    Wednesday, May 18, 2011 11:56 PM