Table Designs

  • kenfred88

    SSC Journeyman

    Points: 76

    Hello,

    I am very new to SQL and am in the process of building my first database.  I have one data set that is 20,000 rows and 500 columns. This is quarterly financial data for multiple companies.  The database will contain 10 of these tables (to start), it will grow to over 40.  This leads to my question.  Would it be best to load the all the data (20,000 rows X 500 columns) in one table for Q1 and the same for Q2, Q3 and Q4, respectively.  Or should the data be divided into multiple tables to increase speed?

    Lastly, is SQL server a secured environment or is it open where others can potentially access your code and or database?

    Thanks.

  • ChrisM@Work

    SSC Guru

    Points: 186023

    kenfred88 - Wednesday, April 25, 2018 7:41 AM

    Hello,

    I am very new to SQL and am in the process of building my first database.  I have one data set that is 20,000 rows and 500 columns. This is quarterly financial data for multiple companies.  The database will contain 10 of these tables (to start), it will grow to over 40.  This leads to my question.  Would it be best to load the all the data (20,000 rows X 500 columns) in one table for Q1 and the same for Q2, Q3 and Q4, respectively.  Or should the data be divided into multiple tables to increase speed?

    Lastly, is SQL server a secured environment or is it open where others can potentially access your code and or database?

    Thanks.

    500 columns will make for extremely difficult coding. Why so many?
    SQL Server has numerous security features. You can lock down to whatever level is appropriate for your data and your customers.

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • kenfred88

    SSC Journeyman

    Points: 76

    Thanks for your reply.

    The reason for the number of columns is 2 fold.  The first is that is how the files are sent over to me in .csv format.  The second is that it includes all the fields in the financial reports submitted by each business.  It is an aggregated table that has all the date in each quarter in one tab in excel.

  • HanShi

    SSC-Dedicated

    Points: 33142

    500 columns is a lot to put into a single table. It is also not a good approach to create a seperate table for each quarter.
    Take a good look at the contents of your data and apply the normalization rules (up to 3NF is commonly used). This will minimize duplicate data and structure the contents accross logical tables. Here's some explanation with a simple example to start with. Search the web for more explanation and samples.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • kenfred88

    SSC Journeyman

    Points: 76

    Thank you

  • HanShi

    SSC-Dedicated

    Points: 33142

    kenfred88 - Wednesday, April 25, 2018 8:06 AM

    Thanks for your reply.

    The reason for the number of columns is 2 fold.  The first is that is how the files are sent over to me in .csv format.  The second is that it includes all the fields in the financial reports submitted by each business.  It is an aggregated table that has all the date in each quarter in one tab in excel.

    Perhaps loading the data from Excel into a staging table can be considered for (mainly) performance reasons. From the staging table you split the data to a normalized database structure. But you could also consider to split the data to a normalized form during the loading process. A tool like SSIS can be used to load the data and split it before writing it to the different destination tables.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • kenfred88

    SSC Journeyman

    Points: 76

    Greatly appreciated!

    Since it was suggested that loading all the data by quarter is not the best approach.  Do you have suggestion on what the tables would look like? To dumb this down a bit for my sake, suppose we have a balance sheet and P&L and supplemental reports with individual fields for each business.  Do you think it is best to list ASSETS as its own table and include all the quarterly data (10 years) in its own table?  Asking as I would then have 500 tables given the number of fields.

  • ZZartin

    SSC-Dedicated

    Points: 30240

    Really it depends on how much you actually know about the data and what you're planning to do with the data downstream.  At the end of the day 20,000 columns per quarter even for 40 tables isn't a very large amount of data for SQL Server to handle.  If all these are are reports you're getting from various companies and people just need access to them there might not be any value in trying to normalize them(assuming that's even possible)

  • ChrisM@Work

    SSC Guru

    Points: 186023

    kenfred88 - Wednesday, April 25, 2018 8:30 AM

    Greatly appreciated!

    Since it was suggested that loading all the data by quarter is not the best approach.  Do you have suggestion on what the tables would look like? To dumb this down a bit for my sake, suppose we have a balance sheet and P&L and supplemental reports with individual fields for each business.  Do you think it is best to list ASSETS as its own table and include all the quarterly data (10 years) in its own table?  Asking as I would then have 500 tables given the number of fields.

    Han Shi has suggested "normalisation" and provided links - have a read, but essentially yes. You want a table for each entity, which will include Company, Assets, BalanceSheet and P&L. Columns for Company and Datetime will differentiate rows - you don't need separate tables for quarters or companies.

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • sgmunson

    SSC Guru

    Points: 110412

    ChrisM@Work - Wednesday, April 25, 2018 8:38 AM

    kenfred88 - Wednesday, April 25, 2018 8:30 AM

    Greatly appreciated!

    Since it was suggested that loading all the data by quarter is not the best approach.  Do you have suggestion on what the tables would look like? To dumb this down a bit for my sake, suppose we have a balance sheet and P&L and supplemental reports with individual fields for each business.  Do you think it is best to list ASSETS as its own table and include all the quarterly data (10 years) in its own table?  Asking as I would then have 500 tables given the number of fields.

    Han Shi has suggested "normalisation" and provided links - have a read, but essentially yes. You want a table for each entity, which will include Company, Assets, BalanceSheet and P&L. Columns for Company and Datetime will differentiate rows - you don't need separate tables for quarters or companies.

    Unless those companies are your customers, and there's a regulatory need to maintain separation of data.

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply