Basic Database Design - HELP

[Mensch]

I humbly ask for assistance in developing a mailing list database for my office. Here is the situation:

We require a database to track subscriptions to 24 different publications and to produce mailing labels for said publications. I've done my homework on normalization but have hit a wall in designing this properly. Help!

Here's what I have thus far:

tblSubscriptionDetail
SubsMonth - Subscription Month
SubsYear - Subscription Year
SubsFee - Cost of Subscription (varies by subscriber and bulletin)

tblSubscriber
SubsID - primary key
IndOrgName - Individual or Organization Name
Contact - Contact Person
Address - Mailing Address
City
State
Zip
Phone

tblSubscriptionType
AllProvider - Receives all mailings
List of 24 Subscription Types

How do I pull this all together so that someone (data entry) can enter in a new subscription on a form (on a COPY of the database, not the "live" version) and then call pull labels for mailing out subscriptions?

 

[lespaul]

I'm kinda confused! Is this a database to track magazine subscriptions that your office receives or a database to track subscription orders that are placed through your office?

Kudos to you on studying normalization before starting, you're a step ahead of the competition!!

leslie

 

[Mensch]

We offer subscriptions to the general public. The subscriptions are subject based and so we set them up by "provider type", meaning an eye doctor only wants (and gets) a subscription to the Vision stuff. Does that make sense? My problem really is how to set up the tables for when Dr. John Doe orders three different subscriptions.

 

[lespaul]

Ok then, now that I have a clue. I would have the following setup:

tblMagazines
Magazine_ID (autonumber or something you make up yourself from the title)
MagazineName
Category_ID (Foreign Key)

tblCategory
Category_ID
Category_Description

tblSubscriber
Subscriber_ID(autonumber)
Business_Name
First_Name
Last_Name
Address
City
State
ZipCode
Phone_Number
(if you have different Billing and Mailing Addresses you will need to change this some (or if you have multiple phone numbers) to have a one to many relationship - one person can have many addresses or phone numbers)

tblSubscription
Subscription_ID(autonumber)
Subscriber_ID
Magazine_ID
Subscription_StartDate
Subscription_EndDate
Subscription_Cost

This is how a many to many is set up in Access. Each person can have many subscription and each magazine has many subscribers. So you set up an intermediate table that stores both the SubscriberID and the MagazineID. You will also want to set up an index in tblSubscription on SubscriberID, MagazineID, and SubscriptionID with no duplicates to make sure that you don't enter a subscription twice. Use tblCategory to make sure that all magazines are assigned a valid Category type. Otherwise you may end up with three of the same category (Vsion, Vision, Vison), if you have Category_ID = 3; Category Description = 'Vision' then you control the data being stored in the database.

I would do the same thing if you create the Phone Number or Address Tables, create tables that hold PhoneNumberType (1 = Business, 2 = Home, 3 = Cell, etc) and address (1 = Billing, 2 = Shipping)

Hope that helps clarify!

Leslie