Go4Expert (http://www.go4expert.com/)
-   MySQL (http://www.go4expert.com/forums/mysql-forum/)
-   -   advice on database schema for stock stuff (http://www.go4expert.com/forums/advice-database-schema-stock-stuff-t18585/)

concept 17Jul2009 15:00

advice on database schema for stock stuff
here is the problem

things that need to be tracked

Number string
segment of number strings location (this will be how i segment the number string)

other things that need to be tracked
who generated the number string meaning
which stock
which industry
which market
which sector

now finally each number string needs to be tracked if it win or loss and where it won or lost at
did it have a loss in
A specific stock
a specific industry
a specific market
a specific sector

Now what i am thinking is making a table for each segment number
so the table would be named segment_1 or X
and the primary key will be the exact number string segment
because that way there will not be duplicate data

now from that what i think i can do is this
is take that specific segment
in a another huge table
that stores the score of the segment meaning win and loss no change
the stock the segment came from
the industry the segment came from
the market the segment came from

Stock DB Schema

Table Stock_location
Key{symbol} | Market | Indystry | Sector

Table Stock_Data
Symbol | Date | Open | High | Low | Close | Volume | ADJ Close | other data such as day win or day loss or whatever else i want

now i can do the following either way what do u think is best or neither

Table Segment_X (x will be the total number of segments of the number string)
Number String Segment {primary key}

Table Segment_Table
Number String Segment | Win | Loss | No Change | segment location | stock symbol | industry | sector | market

__________________________________________________ ___________________

Or solution 2 for number string and segmentation tracking

Table Segment_Table
Segment | Segment location | Win | Loss | no score change | stock symbol | industry | sector | market

I indicated where i placed the master keys other places i did not over look on purpose but that could be due to my kack of expierence

and input will help

thank you

All times are GMT +5.5. The time now is 14:08.