Design Questions

Question

I have a list of "suppliers" and "stores". Each supplier sell "items" to stores, but have special pricing depending on, say, geography or special agreements between supplier and stores. This is represented conceptually as "price groups".

So, for example, item 1 might be $10 for stores that are associated with a supplier's "platinum" price group. That same item might be $12 for stores that are associated with that supplier's "gold" price group.

I have modeled the above concept as follows:

Questions:

1. Is the design normalized? I'm particularly concerned about supplier_price_groups and store_price_groups.
2. Am I going to have problems with this design in terms of scalability, especially when trying to query for the prices of all items of a particular store?

Answer 1

Build your database in stages. You have, so far, three main entities: stores, suppliers, items. The relationship between suppliers and items seems simple enough. A supplier may supply many items and an item may be available through many suppliers. This implies an intersection/junction table between Suppliers and Items. As part of this relationship, the default price, the price in effect if no other arrangements are in effect, can be included.

create tabel SupplierItem(
    SupplierID   int not null,
    ItemID       int not null,
    DefaultPrice currency not null,
    constraint   PK_SupplierItem primary key( SupplierID, ItemID ),
    constraint   FK_SupplierItem_Supplier foreign key( SupplierID )
        references Suppliers( ID ),
    constraint   FK_SupplierItem_Item foreign key( ItemID )
        references Items( ID )
);

Now you have two ways that prices may be adjusted. But let's introduce a third discount simply because it is so common -- the volume-based discount. That can be seen as an attribute of the relationship between Supplier and Item.

create table VolumeDiscount(
    SupplierID   int not null,
    ItemID       int not null,
    MinVol       int not null,
    Discount     tinyint not null check( Discount between 0 and 99 ),
    constraint   PK_VolumeDiscount primary key( SupplierID, ItemID, MinVol )
);

I've omitted the FK definitions that are the same as already defined. Discount is a percentage reduction in price based on a volume >= MinVol and < the next highest MinVol listing. Discount might better be defined as a floating point value -- but this is, after all, for illustration only.

Now let's take the optional geographically-based discounts. This suggests yet another intersection table. It also assumes a table of geographic locations.

create table GeoDiscount(
    SupplierID   int not null,
    GeoID        int not null,
  -- ItemID       int not null,
    Discount     tinyint not null check( Discount between 0 and 99 ),
    constraint   PK_GeoDiscount primary key( SupplierID, GeoID[, ItemID] ),
    constraint   FK_GeoDiscount_Geo foreign key( GeoID )
        references GeoLocations( ID )
);

As you can see, this is easily extendable if there are different discounts for each item, though I don't see that needed very often.

Finally, there are discount agreements between some suppliers and some stores. This can get tricky in that a supplier can give a store a blanket discount for all its items and/or individual discounts on certain items.

create table StoreDiscounts(
    StoreID     int not null,
    SupplierID  int not null,
    ItemID      int,
    Discount    tinyint not null check( Discount between 0 and 99 ),
    constraint  FK_StoreDiscounts_Store foreign key( StoreID )
        references Stores( ID ),
    constraint  UQ_StoreDiscounts unique( StoreID, SupplierID, ItemID )
);

Notice that the natural key (StoreID, SupplierID, ItemID) is defined with a unique constraint rather then a primary key constraint. That is because ItemID is nullable. If ItemID is NULL, the discount applies to all item from the supplier bought by the store. Instead, or in addition, the supplier may offer discounts on a per-item basis. Or one general discount for all items (ItemID is NULL) but different discounts on a select subset of items. In the last case, the per-item discounts may be added to the general discount or it may replace the general discount. There is nothing intrinsic to the design that requires or emphasizes one or the other. Which scheme to use will have to be documented. Whether these discounts replace or are added to the standard volume discounts would be another business rule to be determined outside of this schema design.

This is an untested design, of course, and that last table can probably be improved upon. But the design is fairly "tight" as far as data integrity is concerned and is trivially normalized to BCNF. The queries to work this design will, as may be obvious, fairly complex but that will always be the cost of such desired flexibility. Your job is to make the user's job easier, not your own.