MattioMatt
Registered User.
- Local time
- Today, 22:32
- Joined
- Apr 25, 2017
- Messages
- 99
I'm currently looking to build a new database for a new project I'm working on. At the minute I am in a planning phase for the tables. I'm ideally looking for some help/guidance so I can be planning correctly.
My source data will be a series of flat file Excel documents that I will import into the database. These files already contain an ID field which can be used as the primary key so I do not need to create one.
Excel Doc #1:
AppID
AppName
AppVersion
Description
Status
Excel Doc #2:
AppID
AppName
TestingStatus
Remediation
RemediationNotes
Excel Doc #3:
AppID
AppName
IssueDescription
ReplicatedSuccessfully
Remediation
RemediationNotes
I'll need to upload these documents once a month, however I need to maintain a history of the data of the previous months. The history will need to be available so I can show progress month-by-month through reporting. I'd like to output a table that I can use with a graph that will show progress on various aspects month by month.
1) From some googling I've done, it seems the best way forward is to create a series of temporary import tables and then several queries to lift and shift the data from the temporary tables to their relational tables? Would this be the correct and efficient approach?
2) How could I achieve the history view? This is the part I am really struggling with. I believe I have two options, one is to add a 'DateUploaded' to each record, or my other thoughts have been some kind of archive table/s. Ideally what I need to have happen is, the upload for the current month is the data stored within the primary tables and then the previous data is kept and not over written.
My source data will be a series of flat file Excel documents that I will import into the database. These files already contain an ID field which can be used as the primary key so I do not need to create one.
Excel Doc #1:
AppID
AppName
AppVersion
Description
Status
Excel Doc #2:
AppID
AppName
TestingStatus
Remediation
RemediationNotes
Excel Doc #3:
AppID
AppName
IssueDescription
ReplicatedSuccessfully
Remediation
RemediationNotes
I'll need to upload these documents once a month, however I need to maintain a history of the data of the previous months. The history will need to be available so I can show progress month-by-month through reporting. I'd like to output a table that I can use with a graph that will show progress on various aspects month by month.
1) From some googling I've done, it seems the best way forward is to create a series of temporary import tables and then several queries to lift and shift the data from the temporary tables to their relational tables? Would this be the correct and efficient approach?
2) How could I achieve the history view? This is the part I am really struggling with. I believe I have two options, one is to add a 'DateUploaded' to each record, or my other thoughts have been some kind of archive table/s. Ideally what I need to have happen is, the upload for the current month is the data stored within the primary tables and then the previous data is kept and not over written.