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

[annideon]

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!

[GolferGuy]

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!

[GolferGuy]

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.

[reekjohns]

Check this...ado.net basics

[eckman]

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

[dbeeLee]

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

[lauryfriese]

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.