Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2008
    Posts
    4

    Unanswered: Financial Data Database

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2008
    Posts
    4
    Wouldn't that make a query and display incredibly time consuming though? It would result in a table with perhaps 360 million rows.

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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" ?

  5. #5
    Join Date
    Jan 2008
    Posts
    4
    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.

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Financial Data Database
    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,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  7. #7
    Join Date
    Jan 2008
    Posts
    4
    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.

  8. #8
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by prent327
    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
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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