INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Use of One table vs. multiple tables

Use of One table vs. multiple tables

(OP)
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.

RE: Use of One table vs. multiple tables

2
“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

RE: Use of One table vs. multiple tables

(OP)
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.

RE: Use of One table vs. multiple tables

Quote (Dawnit)

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

RE: Use of One table vs. multiple tables

(OP)
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: roll1

RE: Use of One table vs. multiple tables

(OP)
Done. Thanks!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close