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.
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.
Hi, I'm a newbie here with limited knowledge of database design
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,
ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .