Database Design Help

[eluser]Unknown[/eluser]
Okay guys, I have a project I'm helping a friend out with and we're wanting to allow users to logon and based on their location and current provider, display a list of service plan options and rates (lets just say long distance service for topical purposes).

I'm struggling to design the database to accomodate these features. So far I have the following tables:
- states (id, name, abbreviation) :
- providers (id, name, logo, etc) :
- provider_plans (id, state_id, provider_id, name, details, etc) :

Now, since not only will the criteria be based on state, but also their current local provider, will I need a table of local providers and then another table to associate the state and local provider to the available plans to display? Or how would you guys best solve this issue?

I hope I haven't ran around in circles here and made this somewhat understandable. I will be checking this thread often so if I need to clear anything up just ask.

Thanks so much,

-phear

[eluser]Crafter[/eluser]

Or how would you guys best solve this issue?

The solution is yours. You must map out the data entities and the relationships between them, then resolve many to many relationships by introducing ference tables.

From what I read, your basic requirement is that the state must be the common criteria.
You also indicate that a service provider can have plans based on states,

From this, you will typically go to state -> plans -> providers.

It seems you have the basics right. Perhaps an understanding of what exactly you are truglling with will help us provide suggestions.

[eluser]wiredesignz[/eluser]

From this, you will typically go to state -> plans -> providers.

Surely, Providers is the parent of plans.

States have providers, providers have plans.

States -> Providers is a relationship
Providers -> Plans is a relationship

States -> Plans is a lookup not a relationship

[eluser]Crafter[/eluser]

Surely, Providers is the parent of plans.

From a common sense point of view, yes.

From the provided database structure, no.

[eluser]obiron2[/eluser]
you are almost there...

provider_plans is a X-ref table that normalises the plans.

I am presuming that as state is fixed (you can't change States easily) this will be your point of entry ($STATE).

You can then "SELECT * from provider_plans WHERE state_id = $STATE"
This gives you a list of the plans.

you could then join the provider table so that you can report on provier details

Alternatively if you just want a list of the possible providers you could do

"SELECT DISTINCT provider_id from provider_plans WHERE state_id = $STATE"

which would only give you one record per provider. You would then need to show the available plans

"SELECT * from provider_plans WHERE state_id = $STATE and prover_id = $PROVIDER"


The above structure assumes that a plan is specific to a state and provider. If the provider can offer the same plan in many states, you would want to normalise that data relationship as well

state_provider (id, state_id,provider_id)
plan (id,provider_id)

You can then select a state, choose a state_provider and then from that selection, choose the plan.

If not all plans from a provider are available in all states where that provider operates, then you would need to look at another relationship between plan and state, possibly using an exceptions list (id,state_id,plan_id)

I hope this helps

obiron

[eluser]wiredesignz[/eluser]
[quote author="Crafter" date="1191867351"]
From a common sense point of view, yes.

From the provided database structure, no.[/quote]

The database structure is faulty.

What happens to Plans if a Provder is removed from a State?

Plans are a business entity belonging to Providers they have no direct relationship to State so state_id should not feature in the Plans table.

Providers may also have branch offices in each State, so a relationship for different provider details must also be established.

@obiron2

Also having to do a "SELECT DISTINCT" in this case means you haven't normalized your tables properly.