AccessWannabe09
New member
- Local time
- Today, 02:05
- Joined
- Feb 16, 2009
- Messages
- 1
Hello-
I'm looking for few ideas on how to best design a relational structure for a business process that I've been asked to create. What I have is a flat XML file that I import into access each week that has two types of data...one I will call 'static' and the other 'dynamic'.
The basics of the table are as follows, the table contains a holdings_date, securityID, portfolio, price, amount, security_description, etc. For some of the data it has a functional dependence on portfolio & securityID, for example price & amount (which I am refering as 'dynamic' as it changes each week). The other data (which I refer to as 'static') is functionally dependent only on the securityID such as the security description.
Since I have a lot of data to deal with in order to save space I am trying to create a relational DB so that when I import data each week only the dynamic data is being updated. However, the number of securityIDs can change each week due to buys/sells.
For the process to work correctly I will need to maintain a history of all securityIDs as well as their holding dates (which has a M:N relationship) as well as what portfolio they belong to and their price/amount. for reporting purposes I need the 'static' data.
Any thoughts on how to best design this will be very helpful as I will try each one and post back if they work.
Thank you!!
AccessWannabe09
I'm looking for few ideas on how to best design a relational structure for a business process that I've been asked to create. What I have is a flat XML file that I import into access each week that has two types of data...one I will call 'static' and the other 'dynamic'.
The basics of the table are as follows, the table contains a holdings_date, securityID, portfolio, price, amount, security_description, etc. For some of the data it has a functional dependence on portfolio & securityID, for example price & amount (which I am refering as 'dynamic' as it changes each week). The other data (which I refer to as 'static') is functionally dependent only on the securityID such as the security description.
Since I have a lot of data to deal with in order to save space I am trying to create a relational DB so that when I import data each week only the dynamic data is being updated. However, the number of securityIDs can change each week due to buys/sells.
For the process to work correctly I will need to maintain a history of all securityIDs as well as their holding dates (which has a M:N relationship) as well as what portfolio they belong to and their price/amount. for reporting purposes I need the 'static' data.
Any thoughts on how to best design this will be very helpful as I will try each one and post back if they work.
Thank you!!
AccessWannabe09