Use of One table vs. multiple tables

[Dawnit]

Hi there,
My dilemma is deciding if setting up my particular data in separate tables would be beneficial or make revising this data on an as needed basis by an end user more complicated than if were to store the data in one table.

My data is approx. 50 different records retention periods, which are currently in one table. Examples shown below. These periods could be broken into components and stored in three separate tables, also shown below, and relationships etc. could be built to get these components to work together properly, but would this really be best for this kind of data and the fact that the end users will not be database development savvy?

The retention period examples are:
8 mo.
90 Days After Cert. of Occupancy
AA + 2
AA + 4
AA + 6
AP + 10
AP + 3
AP + 7
C + 5
C + 7
CL + 2
CL + 5
CU + 1
CU + 1 week
CU + 10
CU + 15
CU + 2
CU + 2 or until applicant requests removal
CU + 2 ; retention begins when information transfer to Laserfiche is complete
C + 2
CU + 3
CU + 30
CU + 4
CU + 5
CU + 6
CU + 7
Daily & Mo. Backups
E + 5
E + 6 mo.
E + 7
E + 8 mo.
L + 2
S + 10
S + 2
S + 8
T + 1
T + 4
T + 5
T + 6
T + 7
US + 2

If the above data is broken into components, here’s how I would do it:

Table 1 of 3
RetCode, RetCodeDescrip
A, Active
AA, After Audit
AP, After Paid
CL, Complete
CU, Current
E, Election
L, Life of Asset
P, Permanent
S, Settled
T, Terminated
US, Until Superseded

Table 2 of 3
RetPeriod
Daily
1 week
Monthly
90 days
6 mo.
8 mo.
1 yr.
2 yrs.
3 yrs.
4 yrs.
5 yrs.
6 yrs.
7 yrs.
8 yrs.
9 yrs.
10 yrs.
15 yrs.
30 yrs.
Destroy when no longer useful
Refer to specific type of project/file/record
Until Minutes are approved
Until applicant requests removal

Table 3 of 3
RetNote
Whichever is longer
After Cert. of Occupancy
Retention begins when information transfer to Laserfiche is complete

Thank you so much for any and all input you can offer.

 

[plog]

I would use one table with multiple fields. It would look something like this:

RetentionTypes
RetentionID, RetentionUnits, RetentionValue, RetentionReferenceDate, RetentionNotes
1, Months, 2, Audit, Retain 2 days after audit
2, Years, 10, Paid, Retain 10 years after paid
3, Days, 2, Superseded, Retain 2 days after superseded

You would then use the RetentionID value as a foreign key.

 

[Dawnit]

Hi,
Wouldn't I end up with the same number of records (50ish) in the one table to capture all combinations of type, period and notes, and therefore just have a different version of what I currently have? Or, maybe I don't see the difference of your suggestion.

 

[plog]

Yes you correct. My suggestion is essentially to properly structure your existing table.

What do you see as the advantage to the multiple table method? How would these be brought together again? What table is currently linked to this one?

 

[Dawnit]

I see.

The advantage of muti tables would be in normalizing the database, since so many of the types and periods are repeated. In different combinations though.

 

[jdraw]

When you cross post (ask the same question in different forums) it is considered proper etiquette to
- advise readers that you have cross posted
- include a link to the cross post
http://www.utteraccess.com/forum/Table-Vs-Multiple-Tab-t2010675.html

Here's why: http://www.excelguru.ca/content.php?184

For an excellent tutorial on database design including entities, attributes and relationships and Normalization, please work through the material at
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

Good luck

 

[Dawnit]

I apologize!

I also posted to: http://www.tek-tips.com/viewthread.cfm?qid=1720593

Thank for the explanation link.

 

[Dawnit]

Also, thanks for the suggestion and tutorial link.