title: Database schema help muliple products and suppliers
body: <p>I am hoping someone can help my muddle over a database schema i am trying to implement. It is based around selling mobile contracts and network bolt on from Network providers. The contracts are fore filled by a number of distributors who may or may not sell a networks contract the prices also differ per distributor.</p>
  
  <pre><code>Distributors
  Name                Address
  Distributor 1       address1
  Distributor2        address2
  
  Networks
  Name
  Orange
  O2
  Vodafone
  
  Tariffs
  Network         Tariff          Minutes
  Orange      Business 600        600 Mins
  Orange      Business 100        100 Mins
  O2          Everyday 100        200 Mins
  O2          Everyday 100        100 Mins
  
  Devices
  Name        Make        
  Apple       Iphone
  Samsung     Galaxy
  
  Bolt Ons
  Network Description
  Orange      Web 500mb
  Orange      Unlimited Texts
  O2          Web 250Mb
  O2          Unlimited Texts
  </code></pre>
  
  <pre>    
  Order
  -   Exactly 1 Distributor
  -   Exactly 1 Network
  -   Exactly 1 Tariff
  -   0 or more Devices
  -   0 or more Bolt Ons
  Distributor
  -   0 or more Orders
  -   1 or more Networks
  -   1 or more Tariffs
  o   Unique  Tariff Costs
  -   1 or more BoltOns
  o   Unique Bolt on Cost
  -   1 or more Devices
  o   Unique  Device cost
  Network
  -   0 Orders
  -   0 or more Distributors
  -   1 or more Tariffs
  -   0 or more BoltOns
  -   0 or more Devices
  Tariffs
  -   0 or more Orders
  -   0 or more Distributors
  -   Exactly 1 Network
  -   0 or more BoltOns
  -   0 Devices
  BoltOns
  -   0 or more Orders
  -   0 or more Distributors
  -   Exactly 1  Network
  -   0 or more Tariffs
  -   0 or more Devices
  Devices
  -   0 or more Orders
  -   0 or more Distributors
  -   0 or more Networks
  -   0 Tariffs
  -   1 or more BoltOns
  </pre>  
  
  <p>I have come up with 2 schemas but i am really not happy with it. Mainly due to the     fact that a distributor may not offer all offerings from the network. And also the prices of tariffs, devices and bolt ons also varies per distributor. I was hoping for some suggestions to the schema approach?</p>
  
  <p>Many Thanks</p>
  
  <p>Rob</p>
  
  <p>EDIT--------
  following the comments i have had i have coem up with the following shema. I have added teh following assumptions</p>
  
  <p>Devices and Boltons are similar enough to be in a product table:</p>
  
  <p>The types of query we will need to be running is invoice generation based on what a user may have paid 6-12 months ago - prices for distributors may change monthly.</p>
  
  <p>Number of phones sold per distributor per network etc...</p>
  
  <ul>
  <li>Tariffs are the same across distributors however the price and commission changes per distributor.</li>
  </ul>
  
  <p>Any comments on the below schema?</p>
  
  <pre><code>[Distributors] 
      [Dist_ID] PK
      [Name],
      [Address]
  
  [Network]
      [Network_ID]  PK,
      [Name],
  
  [Tarrif]
      [TariffID] PK
      [Name],
      [Minutes] ,
      [OtherMinutes] ,
      [Texts] ,
      [Data],
      [Term] ,
      [Active] BIT,
  
  [TariffsByDistributor] 
      [TariffsDistributorID] PK
      [DistID]    FK
      [TariffID]  FK
      [RevShare],
      [Commision],
      [Cost],
      [Active]
  
  [Product_Type] 
      [Product_Type_ID]  PK,
      [Name],
      [Details],
  
  [TariffsByNetwork]
      [Network_ID]  PK,
      [TariffID]    PK,
  
  [Order] (
      [Order_Id]      PK,
      [Customer_Id],
      [Date Sold],
      [PaymentStatus],
      [PaymentStatusDate],
  
  [TariffOrders] (
      [Order_Id]          PK,
      [TariffsDistributorID]  PK,
      [RevenueShare],
      [Commision],
      [Cost],
  
  [Products] (
      [Product_Id]    PK
      [Product_Type_ID] FK,
      [Name],
      [Manufacturer],
      [Colour] 
      [Picture],
      [Active] BIT,
  
  [ProductByDistributor]
      [ProductsByDistributorID] PK,
      [Dist_ID]    FK,
      [Product_Id] FK,
      [RevShare],
      [Commision],
      [Cost],
      [Active],
  
  [ProductsOrder] 
      [Order_Id]          PK,
      [ProductsByDistributorID]   PK,
      [RevenueShare],
      [Commision],
      [Cost],
  
  [Products_Network] 
      [Network_ID]    PK,
      [Product_Id]    PK,
  </code></pre>