Database Design Question - A Lot Of Fields Or A Lot Of Tables?
Results 1 to 3 of 3

Thread: Database Design Question - A Lot Of Fields Or A Lot Of Tables?

  1. #1
    Join Date
    Nov 2017
    Posts
    1

    Wink Database Design Question - A Lot Of Fields Or A Lot Of Tables?

    Hello

    I'm looking for help with a database design problem...
    I have 1300 data items, 3300 company tickers, and dates (making this time series data).
    The data items grow by a few a month. The company tickers grow by one or two per week. And dates that grow by one per day.
    The data items can be broken up into logical groups given how they are used in our quant model. There are 10 of these groups.
    We query this data in many ways. These are the most popular I think:
    Single Stock - 50 or so data items for a given ticker
    Subset of Stocks - 5 to 100 data items for a subset of about 3 to 2000 tickers
    Single Stock Time Series - a few years of historical daily data on 3 to 10 data items at a time for a single stock

    In your opinion, how should I design this so it optimizes my queries?

    I'm thinking of having one table per ticker per data item group: 3300 tickers x 10 data item groups = 33,000 tables Which would leave roughly 50 to 200 fields per table (depending on the group) like this

    tblTicker1-DataItemGrp1

    table records
    date1
    date2
    date3
    ...
    dateN

    table fields
    dataItem1
    dataItem2
    dataItem3
    ...
    dataItem1300

    Up to: tblTicker3300-DataItemGrp10

    (I am using numbers and generic names instead of more informative names for simplicity.)

    However 33,000 tables sounds like a lot!! However the alternative is to have a ton of fields, which I am told is super inefficient. Or should I just go with super wide tables with 1300 fields or more, but less tables? What do you think?

    Thanks a ton!

  2. #2
    Join Date
    May 2006
    Posts
    407

    The Root to Good Database Design, NORMALIZATION!

    Read this article: https://support.office.com/en-us/art...4-bd4f9c9ca1f5
    Pay special attention to the last step, "Applying the normalization rules."
    I have been designing and using my designed databases for over 30 years, and the rules of normalization work, and they work very, very well!

  3. #3
    Join Date
    May 2006
    Posts
    407
    And if you want a little more information, here is another article that describes normalization very well.
    https://beginnersbook.com/2015/05/no...ation-in-dbms/

    By the way, I have found using the real names for your data when asking a question in a forum is a big help for me to understand what you are after. What is a Ticker, what is a DataItemGroup? Numbers and generic names really do not simplify a question. At least not for me.
    Last edited by GolferGuy; 12-01-2017 at 08:27 AM.

Posting Permissions

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