Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2010
    Posts
    58

    Stock Market Database - Critique & Normalization

    Hey all, starting a new thread here after my http://www.dbforums.com/microsoft-ac...-database.html thread. Would just like to give a little description of what I'm trying to achieve at the end of this. The database is for independent Oil & Gas companies.

    It is supposed to store data for 5+ years. It is supposed to output 3 main things. Firstly it needs to output a daily market update for all the companies that or on our portfolio, for that it needs to output the following data:

    Name
    Area
    Last Trade
    Change %
    24 Month High
    24 Month Low
    NOSH(Number of Shares)
    Market Cap(m)
    Market Cap($m)
    CEP Index/24 month(1)
    CEP NAV (US$ m)
    Valuation Gap
    News

    The above would be for all the companies on the portfolio, it would basically be a report.

    Secondly it needs to be able to pull out company specific information as well, this would have similar information like the one above but with certain financial information about the company, plus some graphs as well.

    Anyways, have built the table structure for the database and put some sample data in it. Am going to post it up here for all your opinion, need everyone's opinion and need to know if it is sufficiently normalized as well.

    I made sure no data that needs to be calculated will be stored in there, only data such as daily trade price, volume and number of shares will be stored and everything else will be then calculated. Ofcourse some financial data also needs to be stored have got a table for that and then ofcourse there is a table for the historical currency rates from differnt currencies to USD for a better analysis.

    Would also like to ask even though Im still a bit far from this step, have been using Access; is it relatively easy to make graphs in there? Because in Excel it is quite easy, but there doesn't seem to be too much information about how to do it.

    This is made in Access 2007, but the file I uploaded can be opened in 2003 as well.
    Attached Thumbnails Attached Thumbnails Database relationship.bmp  
    Last edited by md85; 07-01-10 at 11:07.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I recommend you post your design either in relational notation, Standard SQL or an image. Most respondents will not download an Access database (which isn't attached currently either)

  3. #3
    Join Date
    Jun 2010
    Posts
    58
    Doh! Was in a hurry going somewhere when I posted the above, gonna post a relational diagram up now.

  4. #4
    Join Date
    Jun 2010
    Posts
    58
    Going to try again, just realized that the size of the file I uploaded was quite large, have a much smaller file now for everyone's convenience.

    Trying to learn, so any critique and suggestions would be greatly appreciated!
    Attached Thumbnails Attached Thumbnails Database relationship-1.jpg  

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by md85 View Post
    Trying to learn, so any critique and suggestions would be greatly appreciated!
    nice diagram, and pretty decent design

    CompanyLocation table does not need its own ID -- give it a composite PK consisting of the two FKs

    why currency code as well as currency id?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2010
    Posts
    58
    Thanks, alot of it is due to the help I've gotten over here!

    Well, I thought rather than using the CurrencyCode in the company information table to form the relation I would use the CurrencyID which is an autonumber field as the FK in the Company_Information table, so as not to repeat the currency code each time for different companies in that table, if that makes sense?

    Well noted on the CompanyLocation table, will make a change.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by md85 View Post
    ... so as not to repeat the currency code each time for different companies in that table, if that makes sense?
    nope

    the currency id repeats just as often as the currency code would have

    i notice that in the FinancialInfo table, you have a column called WhichYear, which i will assume contains a year value, e.g. 2010

    you should not use a surrogate key (autonumber) for currency code any more than you should use a surrogate key (autonumber) for year

    make sense?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2010
    Posts
    58
    lol, thats true!

    I guess that table is rather redundant then, will make a change now, Cheers.

    Edit: Yeah that field in the FinancialInfo table will hold a Yearly value, it's ofcourse for the Annual financial data of the company, again certain information will just be calculated using queries with this as well.
    Last edited by md85; 07-06-10 at 13:12.

  9. #9
    Join Date
    Jun 2010
    Posts
    58
    Have made the recommended changes now.

    Here's the new diagram.
    Attached Thumbnails Attached Thumbnails Database relationship.JPG  

Posting Permissions

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