What table design and relationships to employ to represent stock market indicators such as moving averages?

There are many approaches to this problem. A simple but flexible approach I came up with is to first separate out the indicator into its own relation. Then create another relation to hold the moving average indicators. There is a one to one relationship between indicators and the daily moving averages entities. Why? An indicator record is expected to refer to only a single indicator so the name of the indicator must refer to one and only one moving average. So 21 day moving average indicator must refer to only a single function that determines that particular moving average.

On the other side, partly as a way to explain the intent of the database, we included a price type table. This makes it quite clear that the indicator refers to a type of price data, for example opening price or closing price. It is also clear that many moving average daily records can refer to a single price type.

One interesting outcome of this design exercise is the discovery that the number of days column should be unique. But, why? It would not make sense to have two  moving averages that take the same number of days. By making the column unique we prevent this type of erroneous data input.

A three table stock market price indicators entity relationship diagram that support moving averages of daily open, close, high, and low price types.

But how to use this  model? The idea is use the tables to store persistent data about the indicators and then use them in conjunction with special logic stored in procedures or routines (stored procedures) to produce the moving average results. In a future post we can explore some approaches to doing that.