Hey all, starting a new thread here after my Stock Market Database 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.

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)

Doh! Was in a hurry going somewhere when I posted the above, gonna post a relational diagram up now.

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!

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?

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.

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?

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.

Have made the recommended changes now.

Here's the new diagram.