Hi Guys,
This DB is to collect info following an audit review. There are 3 types of dates collected/stored.
1.Dates that always occur – About 12 dates
2.Dates that occur either under condition A or condition B – 8-10 dependant
3.Dates that sometimes occur and sometimes don’t – 0-20 Dates
I was going to have a table for each. Except for 3, where I was going to have a list-table of all the possible things that could be added (so not to repeat data) and then a table where the ID for that thing and a date are added. All would be related to the audit table to link them up.
This seemed the most efficient, however I will also need to add a list of all dates in chronological order for both the report and to do the audit itself - and I don’t see how I could. Tables 1-2 have the field name as the action and the value as the date, while table 3 has 1 value for the action and 1 value for the date in the same record.
E.G
I’m now thinking it is better to have 1 larger list of actions to include those that would be in table 1/2, and more data in the table 3. This would now include all dates for the audit, albeit at the cost of storing more information in the one table. This would be the table with the most records in the DB, however there is no need for them to remain for longer than 6 months (unlike say sales) so I’m not too fussed.
Is this the best way to store this date data?
This DB is to collect info following an audit review. There are 3 types of dates collected/stored.
1.Dates that always occur – About 12 dates
2.Dates that occur either under condition A or condition B – 8-10 dependant
3.Dates that sometimes occur and sometimes don’t – 0-20 Dates
I was going to have a table for each. Except for 3, where I was going to have a list-table of all the possible things that could be added (so not to repeat data) and then a table where the ID for that thing and a date are added. All would be related to the audit table to link them up.
This seemed the most efficient, however I will also need to add a list of all dates in chronological order for both the report and to do the audit itself - and I don’t see how I could. Tables 1-2 have the field name as the action and the value as the date, while table 3 has 1 value for the action and 1 value for the date in the same record.
E.G
Code:
Table 1/2
ForeignID DateThis DateThat DateTheOther
Value1 01/01/2020 01/02/2020 01/03/2020
Table 3 (With tblDateIDs being a separate table)
ForeignID DateID Date
Value1 1(DateThis) 01/01/2020
Value1 2(DateThat) 01/02/2020
Value1 3(DateTheOther) 01/03/2020
I’m now thinking it is better to have 1 larger list of actions to include those that would be in table 1/2, and more data in the table 3. This would now include all dates for the audit, albeit at the cost of storing more information in the one table. This would be the table with the most records in the DB, however there is no need for them to remain for longer than 6 months (unlike say sales) so I’m not too fussed.
Is this the best way to store this date data?