I'm trying to build a model for my stock market data.
I started with creating a table with just trading days(2 columns- ID and Date) This is to exclude holidays and weekends.
A second Table keeps a record of Stocks/Tickers that I'm interest me. This has 2 colums ID and Ticker
I use the date from Table1 and ticker from Table 2 as foreignkeys to my 2nd table that is DailyStockData. The date and ticker are also my primary keys as both of them make each row unique. (This table has 8 columns- TradeDate, Ticker, Open, High, Low, Close, Volume, AdjClose)
I read that foreign key is used to create a one to many relationship. So I think that would work fine here as each tradedate will have multiple entries (one for each stock).
Beyond the 8 fields, each stock has a list of options (stock derivative contracts) that trade each day. These are called OptionChains. So this has a one to many relationship with each row in DailyStockData.
The real trouble I'm having is with my 4th table, DailyOptionData. this is supposed to store each stocks OptionChains.
Q1.) Since it has a one to many relationship with DailyStockData, should I have a field for TradeDate in the DailyOptionData or is it implicit due to the relationship to the particular row in DailyStockData?
Q2.) Each Option contract is identified with an OptionCode (it's a combintaion of: Ticker+DateOfExpiry+TypeOfOption+ExerciseStrikePrice ) Should I store the OptionCode by itself or have it breakdown into the 4 elements? In the book I'm reading it said not to use compound attributes but rather have scalar values. Is that relevant here?
Q3.) While each stock has infinite life, options are finite because of the expiry date. So if an option is going to expire in 2016 Jan 15th, it will trade each day until then. How can I or should I try to express data in my model?
I'll post my c# code in the comments.
by pope_says via /r/csharp