Question

我希望有人能够帮助我dle忙地利用数据库图象,试图执行。其基础是向网络供应商出售流动合同和网络代谢。合同由一些经销商批准,这些经销商可能或不出售网络合同,每家经销商的价格也有所不同。

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

    
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

我提出了2个图表,但我确实不高兴。主要由于经销商可能无法提供网络的所有产品。关税、装置和代谢的价格也各不相同。我希望对计划办法提出一些建议?

很多成就

抢劫

EDIT-------- following the comments i have had i have coem up with the following shema. I have added teh following assumptions

Devices and Boltons are similar enough to be in a product table:

我们需要管理的类型是根据用户在6至12个月前可能支付的款项生成发票——经销商的价格每月可能发生变化。

Number of phones sold per distributor per network etc...

Tariffs are the same across distributors however the price and commission changes per distributor.

对以下图谋的任何评论?

[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,

Answer 1

你在完成你的计划方面正步入正轨,因为你正在确认分销商与发行商、经销商和定价之间的关系。

我首先在一份分发表上增加与分发商和关税商有关系。然后,检查其他限制,如关税等,并编制表格。

如果你们知道所有的关系规则,你就能够首先在纸面上阐明。如果你同意你的所有规则,我可以进一步帮助你设计你。

www.un.org/spanish/ecosoc 内容提要

它看好。现阶段使贵国数据库的正常化似乎更加复杂,这将节省你们的头痛。 d 提出以下建议:

Standardise whether your table names are plural or not. For example, you have singular "Order" but plural "Products". My personal preference would be to go with singular but that s a discussion for another time.
Rename "ProductByDistributor" to "DistributorProduct" (or the plural if you go that way).
With the joining table names, I tend to find that an "OwnerItem" format reads better. For example, "OrderProduct" as opposed to "ProductOrder".
You can get rid of your tables "TariffOrders" and "ProductOrders". In place of these, you need to add a "OrderPart" or "OrderItem" table. This will have foreign keys which link to "Order", "DistributorProduct" and "DistributorTariff". This removes the duplication of the revenue share, commission and cost data.
Correct your typo of the word "Tariff". It WILL annoy you in future! ;o)

希望会有所帮助。我很高兴地阐述任何问题。

友情链接