Are my Table Sturctures Nomalized and will they do what I want?

[klarae99]

Hello,

I am working in Access 2003 to create a database to record information about an annual fundraiser. I was hoping someone could review my table structure and make sure that it is normalized correctly and that it is set up to do what I would like it to do. I have some doubts on my current table structure and I would really appreciate any suggestions for improvement before I move on to creating my data entery forms.

The purpose of this database is to record infomation for our annual MIC golf tournament fundraiser. We have three different types of supporters for our golf tournament (sponsors- who donate money, donors- who provide us with raffle prizes and golfers- who actually golf). Some sponsors are entitled to free golfers and some golfers are self sponsored only for golfing. In addition some sponsors also donate to the MIC. So I want to create table that record the contact information for each organization and then have a subtable for sponsors and donors (with golfers being a subtable to sponsors). I also created tables to use in cascading combo boxes and for option groups. I think that most of the setup is pretty basic for this type of database, but there is one area that is causing me trouble.

That area is how to set up the tables that deal with sponsorship levels, their fees, and the program ads they are entitled to. My original thought was to create a table for sponsorship level that would include a fee column and an ad size column. This way I could select the sponsorship level and the other two fields would fill in automatically (a combobox, text box cascade). I thought that this would work well because we typically have the same levels year after year (Title, Emerald, Shamrock, Harp, Green/Tee, Foursome, Golfer, In-Kind, Ad Only and Other) But then I realized that this could cause problems if later MICs had a different fee for each level, changing the level table would change the fee in all the old enteries. In addition I realized that some of our sponsorship levels (Other and Ad Only) have multiple fees and some (Other and In-Kind) have multiple ad sizes. Some sponsors do not get an ad but I am willing to use an ad size of none for them. Currently I have tried to set up as three cascading comboboxes but I am hoping there is a better way to do this; Most of my sponsors will have only one chioce in the last two comboboxes and that seems like a waste of time when data entering. I thought about seperating out Other, Ad Only and In-Kind into their own cascades but that seems be go against normalization and would create a lot of fields that do not apply to all sponsors; additionally it would be harder for me to create a report summarizing the Ads I need for the program.

Any suggestions regarding this setup would be greatly appreciated. If you need me to clarify the reason for a certain field or expand on what I hope to do with this database please let me know and I will get back to you. Thanks for your help with this!

tblOrg

OrgID, AutoNumber, PK
Comp, text, Company Name
Add, text, Company Address
CounID, Number, FK
StateID, Number, FK
CityID, Number, FK
ZipID, Number, FK
Phone, Text, Company Phone Number
Fax, Text, Company Fax Number
FName, Text, Contact First Name
LName, Text, Contact Last Name
Prefix, Text, Contact Prefix
Web, Text, Company website
Mail, Y/N, Should we send them mail
TypeID, Number, FK

tblCountry

CountID, AutoNumber, PK
Country, Text, Name of Country

tblState

StateID, AutoNumber, PK
CountID, FK
State, Text, State Name

tblCity

CityID, AutoNumber, PK
StateID, FK
City, Text, City Name

tblZip

ZipID, AutoNumber, PK
CityID, FK
Zip, Text, Zip Code

tblType

TypeID, AutoNumber, PK
Type, Text, Type of Contact

tblSpons

SponsID, AutoNumber, PK
OrgID, Number, FK
PayID, Number, FK
RegDate, Text, Registration Date
MICID, Number, FK
LevelID, Number, FK
FeeID, Number, FK
AdID, Number, FK

tblPay

PayID, AutoNumber, PK
PayDate, Text, Payment Date
MethID, Number, FK
Amt, Number, Amount Paid
Check, Text, Check Number
CC, Text, Creditcard Number
Conf, Text, Conformation Number
Ref, Text, Reference Number

tblMeth

MethID, Autonumber, PK
Method, Text, Payment Methods
CC, Y/N, is it a credit card method?

tblMIC

MICID, AutoNumber, PK
MICDate, Text, Event Date
LocID, Number, FK
Vol, Memo, List of Volunteers at event
Note, Memo, weather, problems, etc

tblLoc

LocID, AutoNumber, PK
Location, Text, Site Name
FName, Text, Contact First Name
LName, Text, Contact Last Name
Phone, Text, Contact Phone Number
Fax, Text, Contact Fax Number
Email, Text, Contact E-mail Address
Addre, Text, Street Address
StateID, Number, FK
CityID, Number, FK
ZipID, Number, FK
Webs, Text, Location website

tblLevel

LevelID, AutoNumber, PK
Level, Text, Sponsorship Level

tblFee

FeeID, AutoNumber, PK
LevelID, Number, FK
MICID, Number, FK
Fee, Text, Cost of Sponsorship

tblAd

AdID, AutoNumber, PK
FeeID, Number, FK
AdSize, Text, Sizes of Available Ads

tblDonor

DonID, AutoNumber, PK
MICID, Number, FK
Donat, Text, What Donated
Value, Text, Relative value of Item
Prize, Text, Prize Group belong to
PTypeID, Number, FK
RegDate, Text, Date Entered
Received, Y/N, Do we have item?

tlbPType

PtypeID, AutoNumber, PK
Ptype, Text, Prize Type (Auction/Raffle)

tblGolfer

GolferID, AutoNumber, PK
SponsID, Number, FK
Name, Text, Golfers Name
Handicap, Text, golf handicap
Team, Text, Number of Team on
Tee, Test, Starting Tee
Winning Team, Y/N, Win this year?
Score, Text, golfers score

[nico5038]

To start with a "down to earth" remark, there's no such thing as a "perfect normalized database". :-)
I often use deliberately de-normalized parts in my databases for better performance or easier coding/form design.
The basic idea is that the data model is a model (and not the reality) that suits your needs and that you know it's strengths and weaknesses.

For designing the table structure it's best to check all the output needed.
When you need to mail the sponsors for ad's you'll need to identify (and record) the needed ad and the date when they accepted/payed for it.
For renewal of the subscription you'll need to have the address and the hight of the fee depending on... etc.
When you need to be able to change fee's, you'll need a start date / end date for the validity of a fee.

Hope this gives a starting point.

Nic;o)

[NeoPa]

Klara, this just seems too much of a question for a forum of volunteers. I don't expect anyone to go to this much trouble for a poster.
I appreciate that you've phrased the question helpfully and well, and I have no problem if anyone feels they want to do this much work for you, but for the record, I would not expect anyone to.

What I can do, in case you haven't come across it before, is to post a link to Mary's Normalisation and Table structures thread and hope that it's helpful to you.