Database design help

[digiduck]

Ok, here's my design problem...

These database holds cellular service data, here's a simplified set of tables to demonstrate the problem.
* Plan
* Device
* Add-on

... and the Relationships
* Add-ons <> Devices (not all add-ons are available for all devices)
* Plans <> Add-ons (not all add-ons are available for all plans)

So far this seems to work fine, now I need to add one more table. "Store". Now a Store contains a subset of Plans, Devices and Add-ons. So now we have these additional relationships...
* Store <> Plan
* Store <> Device
* Store <> Add-on

Now we have a problem with Circular Relationships. Store goes to Plan which goes to Add-on which goes to Store... Is there another way to model this without the circular references?

I'd appreciate any input here.

 

[GeertVerhoeven]

What is meaning of plan in this context?

Greetz,

Geert

Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

 

[digiduck]

This is all centered around Cellular service data, so think Sprint here. Sprint plans, phones (devices) and Add-ons (upgrades to your plans).

 

[GeertVerhoeven]

What about the following solution:

TABLES:
- store
- planPerStore
- plan
- device
- add-on
- add-onPerDevicePerPlan

RELATIONS:
- Store - (1) PlanPerStore
- PlanPerStore - (1) Plan
- Plan (1) - AddOnPerPlanPerDevice
- AddOnPerPlanPerDevice (1) - AddOn
- Device
- Store (1) - Device (Only if device can be soled without plan)

Let me know if you don't understand what I mean.

Greetz,

Geert


Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

 

[johnherman]

I think a better approach might be like this:

CUSTOMER_TABLE
CustomerID
DeviceID
PlanID
Cust Name
Cust Address
etc

PLAN_TABLE
PlanID
Plan Name
Start Date
Sold By
etc

DEVICE_TABLE
DeviceID
MAnufacturer
Model
Support Contract (Y?N)
etc

ACCOUNT_FEATURE
FeatureID
CustomerID
PlanID (may be null if add-on)
FeaturePartOfPlan (Y/N)
StartDate
EndDate
FeatureCharge OR
ChargeScheduleID (link to list of charges and effective dates for those charges)
etc.




-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright