relationships?

[bowks14]

Hello all,

I am using Access to build a database to track cell phone minutes, charges, etc. for a business of about 250 numbers. I have a back end master employee database which I intend to use to keep integrity. After that I am running into trouble setting it up. I need to be able to track each user's particular calling and texting plans as well as their usage and charges. We get a monthly bill that is over 1000 pages with a breakdown for each user.

So basically, how many extra tables should I have and what should be their relationships to one another? I tried a few ways of doing it unsuccessfully.

Thanks in advance.

 

[bowks14]

Bumping this up.

Sorry but I still have not figured this out and would appreciate any tips.

 

[jzwp22]

At this point it is hard to say how many tables you will need, but maybe I can help you get started. As you mention, you will need your employee table. You mention the plans. Do your employees have calling plans that are separate from the texting plans or are they bundled as 1 plan? If the plans are really bundled, you would have a plan with at least 2 types of subplans so that would be a one(plan)-to-many(subplans).

tblPlans
-pkPlanID primary key, autonumber
-fkCarrierID foregn key to tblCarriers
-txtPlanName

tblPlanDetails (the subplans)
-pkPlanDetailID primary key, autonumber
-fkPlanID foreign key to tblPlans
-fkPlanTypeID foreign key to tblPlanType
-txtPlanDetails (not sure how you want to record this)

tblPlanTypes (2 records: calling, texting)
-pkPlanTypeID primary key, autonumber
-txtPlanTypeName


tblCarriers
-pkCarrierID primary key, autonumber
-txtCarrierName

If you could provide some more detail, we might be able to give you some more guidance

 

[bowks14]

jzwp22, thanks for the reply.

In response to your question: "do your employees have calling plans that are seperate from the texting plans or are they bundled as 1 plan?"

I would say no, because users could have a 450 minute plan and a 500 text message plan, while others could have 450 minutes with an unlimited texting. Still others could have a 900 minute plan with 500 texts or unlimited. So to answer, everyone's plan is different.

 

[jzwp22]

Not a problem. Each employee will have at least 2 plans, so that calls for a one-to-many relationship

tblEmpPlans
-pkEmpPlansID primary key, autonumber
-fkEmpID foreign key from your employee table
-fkPlanTypeID foreign key to tblPlanTypes
-txtPlanDetails



tblPlanTypes (2 records: calling, texting)
-pkPlanTypeID primary key, autonumber
-txtPlanTypeName



Since some of your employees will share the same plan details, it might save some data entry time by creating a table that has the possible choices of plans.

For example you might have a table that has records such as this

unlimited
500 minutes
1000 minutes
2000 minutes

The table might look like this:

tblPlanDetails
-pkPlanDetailID primary key, autonumber
-txtMinutes

Then you could join that with the 2 plan types to make combinations

tblPlanMinutes
-pkPlanMinuteID primary key, autonumber
-fkPlanDetailID foreign key to tblPlanDetails
-fkPlanTypeID foreign key to tblPlanTypes


Then you could modify tblEmpPlans to this

tblEmpPlans
-pkEmpPlansID primary key, autonumber
-fkEmpID foreign key from your employee table
-fkPlanMinuteID foreign key to tblPlanMinutes

I'll let you decide which way is more appropriate for your application.