Relationship Questions

[JNJ3120]

Greetings all,

I am creating a database that will enable me to track a company's stocks, options, and bonds. Thus, I have four tables, which are Company, Stocks, Options, and Bonds. Each has its primary key (company name, stock symbol, bond cusip, option symbol). In each table is a respect foreign key targeted towards the other tables, so as to related them. My goal is to be able to look at a company's profile in the database and via a portal or even related fields from related tables have their securities shown. However, I am at a complete lost for creating the relationships. I've attempted it a few times to no avail. I am not sure if I need a join table or whatever the case may be.

Thanks in advance for any and all help, as I am completely stumped.

[comment]

Is there much of a difference between stocks, options, and bonds in terms of the data being tracked for each type?

---

BTW, when you say "company" - is that the company that has issued the security, or one that has purchased it?

Edited January 6, 2012 by comment

[JNJ3120]

Thanks for the response Comment and my apologies for not being clearer. It is for my company. We purchase stocks, bonds, options of other companies. Each type of security has its own uniques features in terms of data being tracked. The primary similarity between a stock, bond, and option entered in this database would be if it were issued by the same company. So basically, what I am looking for is a method to pull up the information of companies whose securities have been purchased and see all of their related securities in one spot.

Thanks again.

[comment]

Each type of security has its own uniques features in terms of data being tracked.

Well, then it should be a simple arrangement of one parent table (Companies) and three child tables (Stocks, Options, and Bonds), with each child table linked to the parent table by CompanyID.

However, this could get more complicated if you also want to track your transactions - since it would be inconvenient to have three separate tables for those.

[JNJ3120]

Comment my friend, you are a genius! That worked flawlessly! I had definitely over thought it. It worked perfectly, I modified the relationships and everything showed up just as it should. I thank you and am quite appreciative of your help.

As far as the transactions, the database has a positions, historical transactions, and balances feature/layouts that is independent in terms of relationships within the database. I would love for it to be integrated, but that is definitely out of my scope of capabilities and needs for right now.

Again, thank you Comment.