Efficient table design

[Joel]

I'm setting up a database that will track the stocks our research analysts
cover. The basic elements will include: analyst id, analyst name, analyst
start date, analyst term date ( this I will include in 1 table); stock
symbol, stock name (separate table); stock symbol, analyst id, coverage
initiated (date), coverage inherited (date), coverage terminated (date) -
separate table or tables. This last table is where I'm getting hung up. If
I use this last table to include all of the date events, it makes it easy to
design queries to show just the stocks that are currently being covered. The
downside to this table design is that I would have date events where some of
the information is not applicable. ie. you wouldn't initiate coverage and
drop coverage on the same day. If I break the last table up (which I
originally tried to do), I have a hard time setting up a query that pulls
only the stocks that are being actively covered. My question - should I use
1 table for all of the date events or should I put the date events in
separate tables (if the latter is the case, how do I design the query to pull
just actively covered stocks?). Thanks.

 

[mscertified]

I'd have a table (AnalystStocks) like:
AnalystID - Key
StockSymbol - Key
DateType (initiated, inherited or terminated)
Date
(The first 2 columns form a unique key)

To find stocks actively being covered:
SELECT StockSymbol FROM AnalystStocks WHERE DateType NOT = 'Terminated'

I'm assuming a stock can only be covered by one analyst and once a stock is
terminated is is never covered again.
I'm also assuming you don't need to keep a history of the dates i.e. once a
stock is terminated you no longer care when it was initiated.

-Dorian

 

[Joel]

Dorian,

I like your suggestions - they are more efficient than what I was trying to
do. The assumptions you had about the data and the intended use of it are
not quite correct - let me know what adjustments I might need to make.
First, a stock is only covered by one analyst but (rarely happens) it can be
covered again once the original termination has occurred (although the new
coverage would be undertaken by a different analyst.

Second, I will be using this database to gather historical data points so
being able to determine the coverage counts at different points in time is
important.

Thanks,
Joel

 

[mscertified]

If you want to capture history it makes the whole task much more complex
because you will have multiple records for each stock and will have to
extract them based on comparing the dates in multiple records.
I'm not sure what you mean by inherited but again I am making an assumption
that coverage starts and ends and you don't care how it starts or ends.
Your table (AnalystStocks) is now:
ID - Autonumber (Key)
AnalystID - links to analyst table
StockSymbol - links to stocks table
DateBeginCoverage
DateEndCoverage

To find stocks actively being covered:
SELECT StockSymbol FROM AnalystStocks WHERE DateEndCoverage IS NULL

It will be up to you to programatically ensure that a stock is only covered
by one anaylst at a time.

-Dorian