Design for a daily stock portfolio db

[Guest]

hi,
I have a question that I hope one of you with more exensive knowledge on db
design can shed some light on.

I'm working on a db in access '02 that stores daily constituents of a
portfolio. The main table is a daily table that holds things such as: date,
cusip (a stocks id), <<these 2 are the primary keys>> stock specific
classifications such as a sector # or index #, price, shares, etc. I also
have other data for this portfolio that is either weekly or monthly in
nature. these are stored in other tables, but without the vital stock
classifications (the sector # and index #) that are used to run all sorts of
queries and reports.

So in order to run queries and reports that are grouped by these
classifications, (btw... how the data is looked at) the monthly or weekly
tables are linked to the daily table by data and cusip so as to retreive the
proper classification.

I know that these classifications should be in their own table to make for a
more efficient and normalized setup as well as faster but I'm kind of stuck
on how I'd be to get each company's classification when I run a daily query
if a classification table only had rows where a company's classification
changed on a certain date.

I have the company names and tickers in another table, but even there, names
and tickers can change through time. The current setup does not best capture
the stock name or ticker through time. There is a many-to-one bet. the daily
data and names, but the name table is really only the current name, I can 't
really get the name or ticker as it might have been say in 1995.

I know this is lengthy, and I thank any of you who have read through it.
I'm fairly technical, but the db design aspect has me a little stumped.

I'm looking for a good design book, but I haven't seen anything that deals
with the aspect of time how it relates to this daily, weekly, monthly data.

Regards,

jbl

 

[Tim Ferguson]

I'm looking for a good design book, but I haven't seen anything that
deals with the aspect of time how it relates to this daily, weekly,
monthly data.

As far as I can tell, if you've got data for every day, it's just a
question of grouping and the appropriate aggregate data. I cannot see the
need for any extra tables, at least from the information you have
supplied here.

Have you tried something like this:

SELECT Cusip,
FORMAT(SaleDate,"yyyy/mm") AS SalesMonth,
SUM(NumberSold) AS MonthlyTotal
FROM MyMainTable
GROUP BY Cusip, FORMAT(SaleDate,"yyyy/mm")
ORDER BY Cusip, FORMAT(SaleDate,"yyyy/mm")

which would seem to do roughly what you want. The syntax is a bit
different if you are using ADO or SQL Server etc, but you get the drift.

Hope that helps


Tim F

 

[Guest]

Tim,

THanks for the note. I've been able to get the data I need, but I was just
trying to find out if there is a more efficient design that will speed up the
queries and reports that I am setting up.