General time series/date-based database design questions

[ArviLaanemets]

I think the table I labeled "tblOtherData" is the one that you believe I'm doing incorrectly (thinking in terms of "columns

I see normalization problems in tblModelData. Not sure how are correlated numbering for VAL and FACTOR fields (when they are correlated of-course), but maybe you split the table as:


tblModelPrices/tblModelDates: MP_ID/MD_ID, Trade_Date, Model_ID, [Model_Price] (Price is optional - to include it or not depends on time it takes to calculate on fly for every report/query).
tblModelParameters: MP_ID, MP_ID, ParamType, ParamValue (where ParamType will be "VAL1", "VAL2", ..., "FACTOR1", "FACTOR2", ... - or integer values representing those types, in which case you may consider having a table where you define those parameters).

A remark: Don't use simply 'ID' as primary key in various tables - after couple of years you don't remember anymore, the ID from which table is used in some procedure. I.e. AssetID, ModelID, PriceDataID (or PD_ID when you don't want too long names for secondary or higher level tables). And use those ID's to link tables, not names connected with those ID's.

With tblOtherData I'm confused! What is "data point" which is generated for every field in table - I see no place for it there? And what are those 1000+ data series in this table for every day? Again I don't see any example in structure, and you say the date is unique - i.e. there is a single row for day. Do you have fields like SERIES1, ..., SERIES1000, ... there? Other than that I don't see any problems in case there are only those 7 fields in table, and all fields are mostly filled.

[cazeek]

A remark: Don't use simply 'ID' as primary key in various tables - after couple of years you don't remember anymore, the ID from which table is used in some procedure. I.e. AssetID, ModelID, PriceDataID (or PD_ID when you don't want too long names for secondary or higher level tables). And use those ID's to link tables, not names connected with those ID's.

Thank you- got it!

With tblOtherData I'm confused! What is "data point" which is generated for every field in table - I see no place for it there? And what are those 1000+ data series in this table for every day? Again I don't see any example in structure, and you say the date is unique - i.e. there is a single row for day. Do you have fields like SERIES1, ..., SERIES1000, ... there? Other than that I don't see any problems in case there are only those 7 fields in table, and all fields are mostly filled.

Exactly- the tables I am showing are just snippets of what the real ones will have. The tblOtherData will have SERIES1, ... , SERIES1200 or so, so I'll need to break it down into several tables (unless I am switching to SQL Server and it accepts more fields). Is this the correct way to go about something like this?

I see normalization problems in tblModelData. Not sure how are correlated numbering for VAL and FACTOR fields (when they are correlated of-course), but maybe you split the table as:
tblModelPrices/tblModelDates: MP_ID/MD_ID, Trade_Date, Model_ID, [Model_Price] (Price is optional - to include it or not depends on time it takes to calculate on fly for every report/query).
tblModelParameters: MP_ID, MP_ID, ParamType, ParamValue (where ParamType will be "VAL1", "VAL2", ..., "FACTOR1", "FACTOR2", ... - or integer values representing those types, in which case you may consider having a table where you define those parameters).

All of those fields (VAL1, FACTOR1,... 75 or so total fields) are calculated independently using other software and Excel each day (not being generated through Access). Each model (indicated by the "MODEL" field, e.g. GCE_R_M) has a set of 75 fields I calculate outside of access every day, and they are all connected/related to each other. I can split if you still think it's prudent for some reason I am not seeing yet?

Ultimately- I would like to create other tables beyond these that are based off of calculations of the data from these tables. Using my image as an example- I may want to know the difference between the "CLOSE" price in tblPriceData and the "MODEL_PRICE" in tblModelData for each available date. It would have to go from the "MODEL" field in tblModelData --> Look-up the "ASSET" in tblModelDef --> Look-up the "CLOSE" in tblPriceData for that "ASSET" using the "TRADE_DATE" from tblModelData --> subtract the "MODEL_PRICE" from tblModelData from the "CLOSE" from tblPriceData.

Explicit Example (from the image): "GCE_R_M" (tblModelData) --> "ASSET" = GCE (tblModelDef) --> "CLOSE" = 1294 (tblPriceData) for 1/11/19, 1306.94 for 1/12/19, 1320.0094 for 1/14/19 --> Subtract "MODEL_PRICE": 1294 - 1312 = 18 for 1/11/19.. and so on.

[Micron]

some of the confusion may be around your use of terms. I see a note "12 fields generated for each asset every day". If this means 12 "rows", you are adding 12 records, not 12 fields.
- if you're going to employ autonumber ids, might as well use them. Thus in tblModelDef you wouldn't see GCE, you'd see 1 - the ID value for that ASSET in tblAssetDef.
- using your colour scheme, I see TradeDate being red; not in any table showing, but in it's own as it seems to be an Entity around which many other aspects revolve, plus it seems to be a primary data point. Maybe tblTradeDate? in which case its PK ID value would propagate throughout the other tables, same as noted for ASSET above
- here's where it can get nuts, for example. To control user selections and data input (thus preventing EUR USD) you'd have tblAssets and combos for picking values from that table. Then the PK ID's (maybe '5' for SIE?) appear everywhere that you're now showing SIE. Sounds convoluted no doubt, but achieves a couple of objectives: 1) reduces field size (a long integer uses less memory/space than a text field for SIE) 2) allows you to propagate changes easily (if you change SIE name in parent table, the 5's representing it automatically refer to the new name). You would need to establish relationships with Cascade updates to achieve such propagation they way you're reusing data now.

To validate your design, you have to start asking several questions like "Can I get these 4 values on a given date from my query?" on paper. If it looks like yes, then build a query for it and see what happens. Also, beyond entering a few records of data directly in tables for testing, you should be entering data in forms. If queries are going to be the record source for said form, build that first. If you can't enter data in the query, a form you build for it is useless. Some here say they only base forms on tables, which I don't understand. The very nature of the table relationships means that if you want to see form records and the fields are spread over 4 tables, do you build a form with 3 or 4 subforms bound to tables?? Not me.

That's all for now, gotta go.

[cazeek]

some of the confusion may be around your use of terms. I see a note "12 fields generated for each asset every day". If this means 12 "rows", you are adding 12 records, not 12 fields

I mean 12 "columns" have a new data point every day per asset. So GCE will have 12 data points, SIE will have 12, etc.

if you're going to employ autonumber ids, might as well use them. Thus in tblModelDef you wouldn't see GCE, you'd see 1 - the ID value for that ASSET in tblAssetDef.

Got it, so basically it is more efficient to use the integers than text. Same goes for the TRADE_DATE- you're saying it is better to create a simple table that provides an ID for each TRADE_DATE and to use that table's PK in the reports that reference TRADE_DATE.

I got to work on everything based on my all of these suggestions and my understanding thus far. I already see very significant improvements in the retrieval speed and file size from my prior process and the horror show I had started a few days ago. I will probably revert back here if I have some more questions/tweaks once I have a better handle on everything rather than getting deeper into the abstract.

Thanks so much again for all of the help!

[ArviLaanemets]

Some limits which can affect your choices!

Max database size for MS Access table (Office 365 or Access 2019) - 2 GB;
Max number of fields for MS Access table (Office 365 or Access 2019) - 255;
Max number of tables in query for MS Access (Office 365 or Access 2019) - 32;
Max number of joins in query for MS Access (Office 365 or Access 2019) - 16;
Max number of characters in query statement for MS Access (Office 365 or Access 2019) - 64000 (32750 when query serves as Recordsource or Rowsource);

Max database size for SQL Server - 2524272 terabytes (this was a surprise for me!);
Max number of fields for SQL Server (regular) table - 1024;
Max number of tables in query for SQL Server - limited only by available resources;

So some of your tables look way off for MS Access, and considering, that those numbers are maximums which can be affected through various limitations, I don't see this working in way you re trying now in SQL Server too.

It looks like moving back-end of your data to SQL Server is a must! And then you have to change a lot jet.

To give some material to think about, an idea:

I don't know exactly, for what those 75 parameters for every model are read daily into tblModelData, but when you move back-end into SQL Server DB, you can write a procedure, which reads this data in. And you can write a Job, which at certain time runs this procedure automatically. You even don't need to save those parameters to table - you read them into variables, do all needed calculations using those variables, and then save results into table. The procedure will be roughly like

...
SET @today = select convert(date, getdate())
--
SELECT @modelid = MIN(ModelID) FROM dbo.tblModels
WHILE @modelid Is Not Null
Begin
   -- Reading in 75 parameters for model with ModelID = @modelid and saving them to variables, e.g. @param01, @param02, ..., @param75
   -- Calculating and storing in variable(s) some value(s) based on 75 parameters,  e.g. @value01 = SomeExpression(@param01, ..., @param75), @value02 = ...
   -- Saving calculated variables into some table(s), e.g. INSERT INTO someTable (TradeDate, ModelID, value01, value02, ...) VALUES (@today, @modelid, @value1, @value2, ...)

   SELECT @modelid = MIN(ModelID) FROM dbo.tblModels WHERE ModelID > @modelid
End

[ssanfu]

As an example, based on the image in Post #15, here are tables/relationships you might start with


Note: tables "tblPriceData" and "tblModelData" have a compound index set to prevent duplicate records.



FYI, "Trade Date" is a poor choice for a PK field. See Microsoft Access Tables: Primary Key Tips and Techniques

[Micron]

I mean 12 "columns" have a new data point every day per asset.

That's the thinking you need to get away from. A table "column" is a field. It matters not if there is only one field in a table, each value is a record in that single field. If there are 12 fields and even if only 3 of them have data in them, it is still one record. "Data points" are for charts. The distinctions might not seem important, but if you want to minimize confusion around your posts, it will help to use the terminology that fits our db paradigms.

I didn't download ssanfu's schema, but it looks good to me. If you're lucky, there's data in it that illustrates the notion of using numeric ID's as fk values.

[ssanfu]

If you're lucky, there's data in it that illustrates the notion of using numeric ID's as fk values

Yep...Data from the image in Post #15.......