Design Questions

database-design

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:

Is the design normalized? I'm particularly concerned about supplier_price_groups and store_price_groups.
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?

Best Answer

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.

Related Solutions

Mysql – Tick boxes and Dropdown

A few points that stand out:

In the table items_options, in your output you have the names like 'Small', 'Medium', 'Large'. If these generic option names are going to be used for different items, as you indicate here:

As you can see items come with same options name but the prices can be different. You notice Balti:Prawns come with extras but Vindaloo:Prawns don't have one.

Then I would change to have an options table, and modify items_options to use the new option_id:

CREATE TABLE IF NOT EXISTS `options` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) NOT NULL,
 PRIMARY KEY (`id`)
)

CREATE TABLE IF NOT EXISTS `items_options` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `item_id` int(11) NOT NULL,
 `option_id` varchar(50) NOT NULL,
 `price` decimal(6,2) NOT NULL,
 PRIMARY KEY (`id`)
)

This way, if you ever change the name of the option, you only have to do so in one row.

Create a new table items_extras to handle this case:

Sometime I want to apply extras groups to an item which mean it will apply to all related options.

CREATE TABLE IF NOT EXISTS `items_extras` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `item_id` int(11) NOT NULL,
 `extra_id` int(11) NOT NULL,
 PRIMARY KEY (`id`)
)

The table groups_extras_options is misleading, because it does not utilize your groups_extras.id column. I would rename the table to items_options_extras, so that it would handle this business decision:

Sometime, I want to apply extras groups to specific options from an item.

CREATE TABLE IF NOT EXISTS `items_options_extras` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `item_option_id` int(11) NOT NULL,
 `extra_id` int(11) NOT NULL,
 PRIMARY KEY (`id`)
)

Now, you should be able to get a list of all extras that belong to all options for a selected item, as well as all extras for the specific option that was selected.

You can further group them as you want by utilizing the groups_extras table.

Using foreign keys would be a good idea, since you are using InnoDB. The idea is to enforce the referential integrity between these tables. As an example, if you add a foreign key on item_option.item_id that references item.id, you let the database handle situations that you cannot have a particular item_option if the item_id does not exist in the item table. You can read more information on how MySQL handles foreign keys here.

Disclaimer: This was done quickly, so there might be other things to change. These are the points that stood out the most, though. Also, I changed your id columns to be singular because that is my practice.

Database Design for Questions and Answers

We have an application at our workplace that does a similar thing. It works by having a table that contains a list of all possible questions like such:

CREATE TABLE QUESTIONS
(
   ID INT NOT NULL PRIMARY KEY,
   SUMMARY NVARCHAR(64) NOT NULL UNIQUE,
   DESCRIPTION NVARCHAR(255) NULL
);

Then you have an ANSWERS and a QUESTIONAIRES table defined using the same structure above. Once you have these two tables you then define a table to hold the list of question/answer possibilties as such:

CREATE TABLE QUESTION_ANSWERS
(
   ID INT NOT NULL PRIMARY KEY,
   QUESTION INT NOT NULL REFERENCES QUESTIONS(ID),
   ANSWER INT NOT NULL REFERENCES ANSWERS(ID)
);

Once you have these you can then create a table to contain the responses as such:

CREATE TABLE RESPONSES
(
   QUESTIONAIRE INT NOT NULL REFERENCES QUESTIONAIRES(ID),
   RESPONSE INT NOT NULL REFERENCES QUESTION_ANSWERS(ID)
);

This will give you maximum flexibility allowing you to add new questions and answers without having to change your database design frequently. It can get a bit complicated if you need to version the questions/answers but this should give you a good foothold to work from.

I hope this helps you.

Best Answer

Related Solutions

Mysql – Tick boxes and Dropdown

Database Design for Questions and Answers

Related Question