Use of One table vs. multiple tables 3

[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 + 2
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
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.

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

“the end users will not be database development savvy”

The end user does not care (should not care) how your data base is designed. IMHO, the end users should not even know how the DB is design and what structure of tables you have.

Do you know how internal combustion engine really works? And what parts are there under the hood in the modern car? I doubt it. Yet, you use and drive your car every day, and if it needs a fix, you go to a
professional (a mechanic).

So, as PHV suggested, I would stick to the rules of relational data base design.


Have fun.

---- Andy

 

[Dawnit]

I guess I was hoping for the simpler way out, which in the end is not the best. Thanks for putting it as you did Andrzejek. Now I know I have to buckle down and become proficient with normalization and relational designs. I'm the not so savvy one now!

Thanks PHV, for the good reference.

 

[dhookom]

Andy deserves a star for a great explanation!

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

I was hoping for the simpler way out

Normalization and proper relational data base designs IS the easy way out. All the rules are already there, you just need to follow them. Trust me, it is the only way to go.

On the other hand, if you start employing some ‘new and improved, outside of proper’ ways to do it your way – even if they do seam to be easier now, in the long run you will get yourself into so much trouble you will wish you would do it right when you started. Believe me, I actually know what I am talking about. To work with improper, wrong, convoluted DB design is a nighmare.


Have fun.

---- Andy

 

[Dawnit]

Another truth that deserves a good star! Thank you for confirming and encouraging me to do it, learn it right the first time.

I couldn't find a gold star, so I hope this will do:

 

[dhookom]

Dawnit,
Just click the "Like this post? Star it!" link.

Duane
Hook'D on Access
MS Access MVP

 

[Dawnit]

Done. Thanks!