Design of a Data Model for a Stock Forecast Database (with Stored Procedure)

Answer 1

If I understand you correctly, what you have is a model that can have zero to many signals. That relation is modified by a coefficient. Your design is lacking a mechanism for relating the coefficients to the model/signal relationship. You have two options. The first is to make that coefficient data part of the Model_Signal relation as follows:

MODEL
-------------------
(PK) M_ID
N_Name
M_Description

SIGNAL
------------------
(PK) SG_ID
SG_Name
SG_Description

MODEL_SIGNALS
------------------
(PK) M_ID
(PK) SG_ID
(PK) Begin_DateTime
(PK) End_DateTime
Coefficient_Value

SIGNALDATA
------------------
(PK) Signal_ID
(PK) begin_datetime
(PK) end_datetime
Stock_Id
Signal_value

The drawback to a design like this would be that if you wanted to just get the relationship between models and signals, absent of the coefficient at any moment in time, the queries become a bit more complex and require a time element to define the relationship. A model that doesn't have this drawback basically has one table to define the model/signal relationship (as you have in your original design) and a second table that has a mode/signal/time/coefficient relationship.

MODEL
------------------
(PK) M_ID
N_Name
M_Description

SIGNAL
------------------
(PK) SG_ID
SG_Name
SG_Description

MODEL_SIGNALS
------------------
(PK) M_ID
(PK) SG_ID

COEFFICIENTS
------------------
(PK) M_ID
(PK) SG_ID
(PK) Begin_DateTime
(PK) End_DateTime
Coefficient_Value


SIGNALDATA
------------------
(PK) Signal_ID
(PK) begin_datetime
(PK) end_datetime
Stock_Id
Signal_value

You will also want to create the appropriate foreign keys to enforce and index the relationships.

Answer 2

Interesting...

In February 2008, during a series of interviews for an application developer position at Arrowstreet Capital in Boston I was asked to respond to this challenge:

Database Design Assignment

Create a data model based on the specifications below. Your data model should include at least the table name, field names, primary keys, and foreign keys. Also, explain how you would code the retrieval procedure described below.

The following should be included as part of the solution: • ERD (Entity Relationship Diagram) • DDL SQL to create all objects (Assume DB is SQL Sever 2005 and Database already exists) • A Small Sample Data Set, including SQL to add to DB • Results of Stored Procedures based on sample data created • During the interview, the candidate should be able to walk us through his understanding of the requirements and his solution. The solution should be returned to Arrowstreet electronically as text based .SQL files. The ERD can be done in MS-Visio if available otherwise a hardcopy will be acceptable.

Specifications:

Create a Stock Forecast database. A forecast involves models, signals, coefficients, and signal data.

First, models must be defined. Each model should have an id, name, and description.

Then, signals are defined. Each signal should have an id, name, and description.

Then, signals are linked to models. Each model can have 0, 1 or many signals. A signal can be in 0, 1, or many models.

A coefficient is a weighting that a signal gets in a particular model. Each signal linked to each model must have a coefficient. Coefficients are defined for a begin datetime / end datetime range (the coefficient values change over time).

Each stock has signal data. A piece of signal data is a defined as a signal_value for a particular signal_id and stock_id that is valid for a specific begin datetime / end datetime range. These begin datetime / end datetime ranges can be different for each signal_id / stock_id combination.

Retrieval of Data:

Write a stored procedure “sp_get_stock_forecast” to retrieve one forecast per stock for a given model and given datetime? You must take all the signals in that model and for each signal multiply signal value at that datetime by coefficient value at that datetime. Then you must sum (signal value x coefficient value) across all signals for that stock.

Your parameters to this procedure would be @ModelID and @AsOfDateTime. The output would look like this:

Stock ID Forecast 85930 0.4543 83493 -0.1844 40033 .78544

One line here makes me a bit suspicious:

Then, signals are linked to models. Each model can have 0, 1 or many signals. A signal can be in 0, 1, or many models.