Table Design Advice

[Gusero]

Hello, I am a database novice. I would like someone to explain the
pros and cons of each design below:


User
uID, uLastName, uFirstName

==== Design A ====

Access List
alMonthlyID, alUserID, alYear, alType

Monthly
mID, mJan, mFeb, mMar, mApr, mMay, mJun, mJul, mAug, mSep, mOct,
mNov, mDec


==== Design B ====

Access List
alMonthlyID, alUserID

Monthly
mID, mYear, mType, mJan, mFeb, mMar, mApr, mMay, mJun, mJul, mAug,
mSep, mOct, mNov, mDec


My first choice was Design A. Mainly because, alUserID will normally
have multiple years and types.
For some reason, the year and type fields seem to apply or rely on the
user more so than the set of months.

From what I have read, adjectives == fields, so I think I have that
part correct. I am just wondering if it
is okay to place them in an outer-related table?

I am now at a road-block with this, and I cannot decide which that I
need. Below is a sample output for 1 user.


John Doe
2008, Basic
$15.99, $15.99, $15.99, $15.99, $15.99, $15.99, $15.99, $15.99,
$17.99, $17.99, $17.99, $17.99

2008, New
-$0.99, -$0.99, -$0.99, -$0.99, -$0.99, -$0.99, $0.00, $0.00, $0.00,
$0.00, $0.00, $0.00

2009, Basic
$17.99, $17.99, $17.99, $17.99, $17.99, $17.99, $19.99, $19.99,
$19.99, $19.99, $19.99, $19.99

2009, Write
$3.99, $3.99, $0.00, $0.00, $0.00, $3.99, $3.99, $3.99, $3.99,
$3.99, $4.99, $3.99


TIA!

 

[Jeff Boyce]

"Designs" are suited to solving (business) problems/issues. What business
issue are you trying to solve?

NOTE: if you use columns named after months, or other "repeating fields",
you have a spreadsheet, not a relational database (i.e., Access). This
design works well for spreadsheets, but causes all kinds of maintenance
headaches if you try to force Access to eat 'sheet data.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Roger Carlson]

Neither is correct. In both cases, your Monthly table has repeating
columns, that is, columns which hold much the same type of information, but
distinguished by the value of the column name. This is a very fundamental
error which will make some types of queries impossible.

Your Monthly table should be something like this:

Monthly
mID, mYear, mMonth, mValue

(whatever "Value" represents, which you haven't said.)

So, with data, it would look like this:

1, 2008, Jan, $15.99
2, 2008, Feb, $15.99
3, 2008, Mar, $15.99

BTW, I'm not saying this is the correct design, because you've left out a
lot of information about what things really mean. I'm just giving you an
example of how you can correct the repeated columns problem.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Gusero]

I would like to thank both of you for the quick replies.
Both of which are pointing me to go in a different direction.

I was unaware of "repeating columns" as being a no no. The data that I
am working with originates from an excel sheet, as you might could
have guessed.

Let me re-think my approach, and I will come back with another
question

 

[Roger Carlson]

I would encourage you to look into the topic of Normalization. If you
google it, you'll get a lot of hits.

Here's my own take on the subject:
http://rogersaccessblog.blogspot.com/2008/12/what-is-normalization-part-i.html,
which discusses the topic of what happens when you do not normalize. It's
followed by several posts that discuss how to normalize your database.

You can download the entire series here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=238. That page
has a number of tutorials to step you through it, too.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Gusero]

Upon choosing a different direction, I am now pondering the question
"__Is a database even needed in my situation?__". So, here are some
further details:

The company that I work for was asked to provide a figure (insurance
related) that totals the company's contribution minus the employee's
contribution for each of the different types of insurance offered.
Might I add, this figure had to be done two weeks prior to when it was
requested.

All of this was brewing a crash-course spreadsheet in a matter of
about two days. So, with the help of another co-worker, we were able
to produce these figures within a close enough time window. Now that
the dust has settled, I am looking for a way to improve this process
for when the next request comes due. So to answer Jeff's question
"What business issue are you trying to solve?", I'm not really sure
how to answer your question...

In the past, I have seen where databases are used for companies to
keep up with things such as this, but I have never had the opportunity
to be deep enough in to examine any of the details involved. So
again, "Is a database even necessary".

From the looks of it, the results of the current spreadsheet look
nothing close to fitting the design process of putting it into a
database.

I studied Roger's example, and I made my data conform to a similar
design. After entering the data from only about 20 of the employees,
I compared the file sizes of both the excel file and the access file.
This size difference is one of the biggest factors to my question.
The completed excel file was 140kb, and the access database file was
nearly 4.5mb (with data from only 20 employees). This may be normal.
I was not sure, but it does not look good from my point of view.

"A lot of confusion originates with when to use a spreadsheet and when
to use a database. - (1)" And, this confusion continues to grow for
me, at this point... Up to this point, my company has been one of the
worst to create a database inside of an excel file. Even though I am
not skilled enough with databases to know for sure if it is necessary,
I am bound and determined to break this habitual barrier for the
greater good.

Another statement from her tutorial: "Spread sheets are generally used
for financial and other applications where calculations, graphs, and
"what-if" analysis will be used. - (1)" brings me even deeper to my
main question. Just because we were able to pull the figures that we
were needing at the time with a spread sheet, is this good enough?

I feel like this is enough rambling for now, but I am most eager to
implement an access database for the company. I am most grateful to
those of you who have responded to my previous post. I truly admire
your experience and desire to help other people with this wonderful
tool.

Below is the newest structure that I am currently working with:


Structure:
----------

Employee
eID, eFirstName, eLastname, eMiddleInitial

Insurance
iEmployeeID, iYear, iMonth, iType, iAmount, iAdjustment, iRemark


Sample Data:
------------

Employee
1, John, Smith, E
2, Jane, Doe, Q

Insurance
1, 2009, January, Health, $123.45, $0.00, (Null)
1, 2009, February, Health, $123.45, $0.00, (Null)
1, 2009, March, Health, $123.45, $0.00, (Null)
1, 2009, April, Health, $123.45, $0.00, (Null)
1, 2009, May, Health, $123.45, $0.00, (Null)
1, 2009, June, Health, $123.45, $0.00, (Null)
1, 2009, July, Health, $123.45, $0.00, (Null)
1, 2009, January, Dental, $123.45, $0.00, (Null)
1, 2009, February, Dental, $123.45, $0.00, (Null)
1, 2009, March, Dental, $123.45, $0.00, (Null)
1, 2009, April, Dental, $123.45, $0.00, (Null)
1, 2009, May, Dental, $123.45, $0.00, (Null)
1, 2009, June, Dental, $123.45, $0.00, (Null)
1, 2009, July, Dental, $123.45, $0.00, (Null)
2, 2009, January, Health, $123.45, $0.00, (Null)
2, 2009, February, Health, $123.45, $0.00, (Null)
2, 2009, March, Health, $123.45, $0.00, (Null)
2, 2009, April, Health, $123.45, $0.00, (Null)
2, 2009, May, Health, $123.45, $0.00, (Null)
2, 2009, June, Health, $1234.56, $567.89, Added family
2, 2009, July, Health, $1234.56, $0.00, (Null)


Reference(s):

 

[Fred]

There's somehting else at work if your little DB is 4.5 meg. Most likely you
just need to "compact and repair"

Your application is probably better off done in Access IF you wish to make
the effort to learn Access.


From a pure DB design standpoint, your new design looks fine. But the open
question is whether or not it is storing the info / doinig what you need it
to do.

It's important to recognize and choose what "entities" you are databasing.
In your new structure, these are:

Employees
Instances of Monthly Payments for Insurance (and with the only informaiton
about what insurance (or insurance policy) that it is for being the words
"Health" or "Dental". If that's all that you need, you're fine.

 

[Gusero]

Thank you for the input Fred. It ensures some of my reasons for
choosing to create the Access DB.

I suppose it was rather silly of me to ask the opening question from
my last post here. Sure, someone could answer it, but I am starting
to realize the value of being able to answer that question for myself.

I'm not exactly sure what was causing the file to be 4.5mb, but after
creating a new database from scratch, it seems to have lowered some.
It now includes all of my data which is almost 2,600 records, and the
file is right at 4.8mb. I may or may not be able to get that smaller,
but this is not an issue right now.

I feel more comfortable now than I did when I started. Much thanks to
you all!

 

[Fred]

Access DB's get blank space in them and grow, e.g. when you make and delete
objects like forms, reports etc, their blank space remains. Running
"Compact and Repair" squeezes all of that extra air out of your DB

Either way I wouldn't be worrying about a 4 meg file. Multiply that by 500
and then you have a problem.