Results 1 to 7 of 7

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.

  4. #4
    Join Date
    Oct 2019
    Posts
    1
    Check this...ado.net basics

  5. #5
    Join Date
    May 2021
    Posts
    1

    Arrow

    Quote Originally Posted by annideon View Post
    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. <SNIP ...> how should I design this so it optimizes my queries? <SNIP ...>

    Even though the original question was posted 3 years ago, I contribute the following for future reference. I agree with @GolferGuy , follow standard relational database design standards, and normalization techniques. Use the links I post below to optimize the Jet Database Engine (or Access Database Engine), until you've outgrown it. Then migrate to a faster engine, like SQL Express, MS-SQL, Oracle, mySQL, Postgre SQL, etc.

    Microsoft Access Performance Tips
    Speeding Up Microsoft Jet database engine Operations
    Use Jet Engine Optimizations to Speed Up Queries

  6. #6
    Join Date
    Jun 2021
    Posts
    1
    I'd also like to point out that an Access database has a 2GB file size limit, and 255 fields per table. You can not have 1300 fields in a single table. Total objects allowed in the database is 32,768. Microsoft Access is the wrong solution for your requirements. You need to use something else.

    https://support.microsoft.com/en-us/...more%20rows%20

  7. #7
    Join Date
    Dec 2023
    Posts
    1
    Quote Originally Posted by dbeeLee View Post
    I'd also like to point out that an Access database has a 2GB file size limit, and 255 fields per table. You can not have 1300 fields in a single table. Total objects allowed in the database is 32,768. Microsoft Access is the wrong solution for your requirements. You need to use something else.

    https://support.microsoft.com/en-us/...more%20rows%20 Candy Crush
    Thanks a lot.

Posting Permissions

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