Hello all, I have been tasked to design a database for a small private equity firm. I have very little experience in designing databases, but I did basic SQL and have got a grasp of programming. I'm a quick learner and have been given enough time to learn, design and build up the database.

Basically the database, should encompass all Oil & Gas independent companies. To start with they want the 70 odd companies that they are following. The list of requirements for it are as follows:

Market Info
Company name
Listing
Code name
Currency
Share Price, last close
Change %
24 month High - date
24 month low - date
Price Chart (Graph showing trend)
Free float (Shares outstanding)
Avg. Volume


Asset Info
Countries where they have assets
Concessions
etc

Financial Info
Sales
Rates

Documentation
Press Releases
Analyst review
Market Research
(Basically any old & new news related articles that are relevant to a company)

CEP Review
CEP Valuation
(This is basically the companies own index with which they judge how a company has been performing based on a formula)


There are 3 main objectives that need to be achieved at the end with this database. There is a daily market update which needs to be provided at the end of each day; this is for the 70 companies that are on the portfolio being followed by the firm, it would include most of the information in the Market info table plus the CEP review. The second would be to be able to pull up all the data for a single company at any time. Thirdly would be able to come up with a comparison graph showing the performance of two or more different companies.

Now as I said I have a basic understanding of SQL and can form tables and join them, I have looked at the different solutions that are there for this purpose, have looked at MySQL, would that be able to do all of the above? Any good tutorials or books that you could recommend as well would be very helpful. Any tips on how to go about it would also be greatly appreciated. What would be the best way to update the share price? Doing it manually would be a waste of time; someone mentioned a website such as xignite.com which provides you with the data, is there a way to pull the data off of say Bloomberg for example.

Thank you
MD

Edit: Is MS Access any good at this?
Edit 2: I have been going through some MySQL tutorials, so far so good, the only trouble is I can't seem to work out how it would be possible for me to output a trend graph for a company, unless I had 5 years worth of historical data stored in it, have just been trying to wrap my head around what sort of table I would need, to store that kind of data.

There are litterally thousands of stock tracking packages available. Quicken Premier could do what you've described "out of the box" for a tiny fraction of the cost required to do it yourself.

What differentiates what you are doing that would add enough value to make this worthwhile for your company?

-PatP

Well, this is actually an un-paid internship that I'm doing right out of college with a bonus at the end and a position if I perform well, they want me to basically design and manage a historical database of their portfolio to start with, plus eventually have all Oil and Gas companies on it as well.

Buying a packaged software is also one of the options that was mentioned. I firstly have to compile a report for them, show them the different options that are available and what different routes I could take, which is why I've even talked with actual developers to gauge what sort of price they would charge as well.

This package seems very interesting, would I be able to add my own formulae to it? This is for the companies own Index with which they gauge a companies performance.

At the moment the company is using Excel for their daily Market updates.(I can email you an example of it if you care to have a look) Any package we purchase would have to be very flexible in the amount of changes that we could make to it, for example we also want to be able to put news articles and analyst report links into it as well.

Thank you for your suggestion.

You know nothing about designing databases, but you got an internship designing databases?

lol.....the internship wasn't specifically for designing a database, I joined because I'm trying to switch from my engineering background into finance, my position is as an Analyst where I sift through data and news and provide the big guys with the right information. I actually suggested to them after joining why they were using a very old-school system for their market updates, which is why they gave me this task.

If they are willing to give me the time to learn on the job, why not?

Like I said though, they are open to a number of different options, so a packaged software which can handle all of the above would also be great.

I'd think MS Access would be fine and appropriate for what you are doing.
But MS Access 2007 sucks horribly, and I have personally vowed never to develop anything in it.
If you can use an older version of Access, you will find it much easier to create your database application and analyze the data.

i want your job

Its an internship, remember?

Probably stills pays better than your existing gig, though....

Thanks for all the suggestions guys. Just having a few issues with how I should store the historical stock market data. This is especially important to generate the comparison graphs between different companies. Should I have a seperate table for each company(which would be rather cumbersome), or any other tips? Would it be possible to achieve this using Access?

Basically need to store the value for each stock for everyday for the past 5 years.

Lord no.

One table for your companies. One table with a foreign key related to this for your companies value over time. So it would have a composite key of (company_id, date). One row per company per day.

Access should be fine.

Hey all, thanks alot for the help so far, Im beginning to understand Relational databases alot more now.

I think my database so far seems to be sufficiently normalized, there are no dupilcate fields but have just hit a snag.

The way I've done it is as you suggested; have a company table with certain information that does not change; a seperate locations table because a company can have exploration and mining licences in many places, so this forms the relationship with that, a financial info table showing their annual financial details for the past few years. Then there is ofcourse the historical stock market data table which has a composite key of (companyID, Date) so that insures that each row is unique. Now I've come to a point where I need the market caps or any other values to be in USD instead of the different currencies that they are in.

We all know that, every day the currency changes ofcourse, so I thought I would have another table labeled HistoricalCurrencyData, and have a composite key with (CurrencyCode, Date); I've already inputted one set of currency data into it over a range of two years, but when I try to assign a composite key to it, it gives me the duplicate error, saying that it would create duplicate values in the index, primary key or relationship. At the moment it is just a floating table without any relationships so Im not sure what the issue with it is. Should I assign it an autonumber primary key and then form a relationship between the CurrencyCode in the two different tables?

Also, should I add the CurrencyCode to the company table or have a seperate table with the currency codes and ID's and then have a CurrencyID field in the Company table?

Oh and btw, had to use Access 2007, thats what was already there on our computers.

Create your indexes and constraints before adding data.

Cheers for that, how should I link this table with the main company table though. Should there be a table in the middle linking the HistoricalCurrencyData table with CurrencyTable with ID and CurrencyCode, and then have the CurrencyID linked with the main CompanyTable?

So in essence the middle table would have a one-to-many relationship with both the CompanyTable and the HistoricalCurrencyData table. Am I thinking this through right? Im not too sure about it.

Hey all, thanks alot for the help that everyone has given so far. I think the look of the tables of the database now is quite good and very normalized. There isnt any duplicate or redundant data in it.

I have now come to the queries part; firstly I would like to know how to display data together in one field instead of in differen fields. For example; a company called Premier Oil has licences in 3 different countries, with the select query that I have made it outputs the same data but all in different rows like so:

CompanyName | StockCode | NOSH | Locaion

Premier Oil | PMO.L | 115mn | UK

Premier Oil | PMO.L | 115mn | Indonesia

etc...

I mean I would rather it outputted the data in the following way if possible:

Premier Oil | PMO.L | 115mn | UK, Indonesia.....

Secondly I've worked out DMAX is there to select the highest value over a certain time. Whats the formula if I want to select the last value and then use it in a calculation? This would be for the Historical Stock Data table which has a composite (StockCode, Date) key, so I would want to select the last possible date to calculate the Market Cap for that particular day.

Cheers!

Edit: Would like to add, that the above where I'm asking for the information to be grouped together, the information is taken from two differen tables; One which is purely for CompanyInformation and the second which purely has Locations; there is a third table which I use to form the association between these two tables. This is because many of these companies share locations, many have multiple locations so I thought it was best to do it in the way I did, correct me please if Im wrong?

I would recommend you start a new thread in the Access forum (assuming you are using Access). This is no longer a database design issue.