[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
