Schema for product prices based on membership level

Presumably the Disp_id is a non-unique field as you'll have an order table which contains the customer name / delivery address etc

so you'll have an order has 1-+ dispatches The product table will cant be a 1-1 relationship with dispatch otherwise only one person would ever be able to order that product regardless of how many they order so the product -> dispatch needs to be 1-+

looking at the information here this is less a table you want to create and more a View since all that information will be stored in other locations you dont want to duplicate things, you just want to derive the locations

A view based on the

[Orders]-> OrderID (Is the dispatch ID)
[Orders]-> Product ID -join-> [Products] -> ProductName
[Orders]-> Product ID -join-> [Products] -> Shelf Location
[Orders]-> ProductQuantity

(I used to work in a warehouse and thats how it used to work there and makes sense, and fuflls all the needs that I can see you wanting)

There's no reason to load all of the products, you want to just load the products of an order

Think of the Dispatch as part of the Full order, the warehouse staff only needs to know that this gets picked up from there and gets placed with other items of this dispatch ready to actually be dispatched

Hope that helps (it is based on my understanding of what I've read) Ste

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.