Best table arrangement for this date data. (1 Viewer)

Firepen

New member
Local time
Today, 09:03
Joined
Aug 4, 2020
Messages
7
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

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?
 

plog

Banishment Pending
Local time
Today, 04:03
Joined
May 11, 2011
Messages
11,611
This DB is to collect info following an audit review. There are 3 types of dates collected/stored.

Usually the best first step in figuring out table structure is write a paragraph describing your organization in plain english. Then you take the subjects/nouns from that paragraph and those become your tables.

Using your above quote as that paragraph I see just 2 subjects/nouns--Audits and Dates. Because its too generic, let's change 'Dates' to 'Events', then with what you've described those are the only 2 tables you need:

tblAudits
auditID, autonumber, primary key
...
other fields, ie. organization being audited, auditor, etc
...

tblEvents
eventID, autonumber, primary key
auditID, number, foreign key to tblAudits
eventDate, date, date this event occured.
eventType, string, description of type this event is


With that structure, it accomodates every type of date you have whether they are required or optional. The real test is that if you add a new Event/Date to the audit process you don't have to revist yoru table structure with what I laid out, with what you proposed you might.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:03
Joined
Feb 19, 2013
Messages
16,553
data should be stored vertically, but your example is so vague I can't work out what is supposed to be what.

I think you are saying something like 'to complete an audit, the audit process has a minimum of 12 defined actions which need to be completed and for which we set dates. In addition a further 8 to 10 actions are conditional on something and need to be diarised? And anything up to another 20 events may also need to be diarised. These apply to all our clients'
 

Users who are viewing this thread

Top Bottom