Normalizing / Denormalizing

We have a database of about 600,000 live (active) items.

So far each item has one and only one price at which it is sold.

SOME items (not all of them, a minority) will eventually sell at different prices in SOME stores.

I suggested to add a table:

iditem
idStoreType
price

And insert here ONLY the items that have a different price at the special store.

If the idStoreType/iditme combination is NOT FOUND, then the selling price for this item is the one already stored in our 600,000-item table at the "price" column.

Another person suggests that we should create a table:

iditem
idstoretype
price

And add there all 600,000 items for the time being and use this new table from now on. If prices change for another store or store type, then all 600,000 rows of the new

iditem-idstoretype-price

should be inserted, even though 98.5% of all rows will have exactly the same price for the same combination of IDITEM-ITSTORTYPE.

How should this be managed? Also, consider the fact that items with a different price at special stores will eventually go back to the same "central" price.

Thank you for any advise on this.

My suggestion includes the creation of a view that will yield the selling price, giving always the right one. Could also be a procedure.

Sounds like some type of discount system.

If so, will the discounts be applied across the board to all items in a store or on specific items?
My quick idea if you absolutely know that it's on a per store basis you might be able to create a new table by storetype and a discount percentage. If not you can create a discount item table with storetype, itemid and discount percentage.

Then when you get the price just multiply the discount percentage with the actual price, and use COALESCE with 1 as the default for when there's no discounted price.

Not sure if it will work in your case, but that's what popped into my head as a probable quick solution.

-b

I see no problem with the original concept of a small table that only has information for products with altered prices.

It's not a question of normalization, but of logical design. Normalization only answers a small part of the overall questions needed to design a database for serious operations.

This is actually a classic approach to the "missing information" problem: Most of your stores don't have a special price, so why create a record to say that fact? And, this solution can grow in both directions. If it seems like many stores will have special prices, grow the table. Or, if you are phasing out these special prices, shrink the table.

The only thing to be careful with is your logic in processing orders, adding products, or updating products. You will definitely want to use view with well-tested queries, and maybe a trigger or two. You need to make sure that the "special" prices can't be circumvented accidentally by your application.

Performance might be a problem in all this, though, especially if there are many special prices. Hmmm... maybe I am missing somthing...


Are these changed prices different for each store, or will all stores with a different price for a certain product have the same different price?



idStoreType would lead me to think that you are applying different prices for a certain product to a group of stores. Or should that be idStore?

Depending on the answer to the above two questions, there might be better-performing ways to deal with the problem.

Expressing your design in terms of a predicate would be useful here.

Your perception is correct. The table would be:

iditem
idStoreType (group)
SellingPrice

iditem,idStoreType are unique (primary key) on this table.

The order of events would be:

1) The item is normally entered in the database with a regular selling price.

2) If the item should be offered at higher or lower prices at one or more groups of stores, then a row would be created for each exception.

3) The application will simply require the selling price of a specific item at a specific idStoreType. If the result is NULL, then it will simply request the price from the original, huge table that contains all prices.

This can be solved with a view which consists of a union of two record sets: one in which the idStoreType is always null and the other in which it is NEVER null.

select price from vwprices where
(idGroup=? and idItem=?)
or
(idGroup is null and idItme=?)
order by idGroup desc

The record set will consist of at the most 2 rows. We are interested in the first one, thus the "order by" clause.

There shouldn't be a performance problem, since the view will cause the plan to use the indexes (unique in both cases).

Most of the time the first part of the query will bring nothing, and ONE and only ONE item from the second part will always be present, since all items do exist on the main, huge table.

What do you think?

The union would be something like this:

create view vwprices(idItem,idGroup,price) as
select idItem, cast(null as smallint) as idGroup, price from main table
union
select idItem, idGroup, price from smalltable

What do you think?

Looks good to me. To comment in any further detail I would need to know more about your logical model, business rules, etc... but I think you are on the right track.

I don't think that a view is a very efficient way to go here because from what I understand of your situation, there is a cartesian join between store and item.

Does your database support functions? If so, I would do it that way.

E.g.



This example is in pl/sql (oracle), but you could do this in any db that supports functions.

If your db doesn't support functions (boo) then it's probably most efficient to code this in your application layer. That makes it two separate db calls, but doesn't load your db down as much.

This:



can become this:

select price into price from special_prices
where iditem=:iditem and idgroup = :idgroup

iditem + idgroup ARE the primary key of the special_prices table.

No need for any of the other comparisons.

The program already knows the IDGROUP and the IDITEM (idgroup is a variable that takes a value as the application starts, and iditem is obtained when the item's barcode is read).

That's why the view is efficient. The plan used shows that it will only use indexes.

Did I miss something?

I ran the following:

create table specprice
(
numart integer not null,
group smallint not null,
primary key (group,numart),
price double precision
)##

insert into specprec select numart,4,regprice*.95 from pvartics where numart>900000 and rownum < 1000##

create view vpre(numart,group,price) as
select numart,cast(null as smallint) as group,price from pvartics
union
select numart,group,precio from specprice##

explain plan for
select * from vpre where (numart=600500 and group=6)
or
(numart=600500 and group is null) ##

--

The plan always uses primary key indexes. Response is instantaneous.

Table PVARTICS contains 1.2 million rows. Table SPECPRICE contains about 6,000 rows.

The view does work. This is from a real database where the small table was added and the insertions were made for experimentation.