Hi, I'm a newbie here with limited knowledge of database design, so I appreciate any help in advance. I'm building out a database that is designed to store financial data for roughly 20-30k entities. For each entity I will store prices (avg. of 5,000 rows per entity) and data (avg. of 12,000 rows per entity). Both prices and data will generally be queried on an entity by entity basis, which would argue for creating separate tables for each entity, but they will also sometimes be queried and analyzed in other ways. I guess my two questions are is there a limitation to the number of tables in a database and how best does one manage potentially tens of thousands of tables? and for other complicated queries would I use joined tables and how exactly do those work? Thanks.

use one table for all entities, i.e. one table for prices, a second table for data

separate sets of tables for each entity is inviting heartache, frustration, and ultimately sheer chaos

Wouldn't that make a query and display incredibly time consuming though? It would result in a table with perhaps 360 million rows.

As opposed to the magical guess process so finding the right table?
It all depends on your indexes/keys and what data you're looking for (a where clause always helps).

Tell me with separate tables how you expect to concatentate all the results for the top 20 "entitites" ?

My plan was to have an index table that lists general information on each entity, including an identifying number. Then each price and data table will be a concatenation of the identifying number and the word "price" or "data", making finding the specific table needed easy. As for running queries across tables, that's obviously where this structure becomes a problem and I was hoping a joined table might work.

I don't blame you, but knowing that you are a newbie and that you have to deal with millions of rows of financial (that is, very very important) data, you should IMO tell your boss that he NEEDS to hire someone who KNOWS database design. I don't say that you can't learn how to do and that we cannot help you, it will take months before you can design database schemas correctly (and knowing why), enforce all needed constraints and optimize data access / batch processing for so many rows... Furthermore, you are on your own ! With someone having years of experience beside you, it would be : much quicker / much better for your boss and much quicker for you to learn.

As for your question, as it already has been said, you should never had even thought of "tens of thousands of tables". Never EVER, even in your worst nighmares .

That being said, if you want us to help you with your design, please be more precise on your needs : entities and relationships (as you think they should be for now), fields, other constraints...

HTH & Regards,

rbaraer

Thanks for the reality check rbaraer. Unfortunately, this is my own project, so I'm on my own. Rather than pester the forum with dozens of questions, I'll seek out some outside consultancy help.

That seems a wise decision to me. Yet, I hope I won't have discouraged you from asking questions on this forum : if you have questions in the future, don't hesitate !

Maybe Rudy (r937) can help you as a consultant ?

Anyway, good luck in your project !

Regards,

rbaraer