database design suggestion needed
- by JMSA
I need to design a table for daily sales of pharmaceutical products.
There are hundreds of types of products available {Name, code}.
Thousands of sales-persons are employed to sell those products{name, code}.
They collect products from different depots{name, code}.
They work in different Areas - Zones - Markets - Outlets, etc. {All have names and codes}
Each product has various types of prices {Production Price, Trade Price, Business Price, Discount Price, etc.}. And, sales-persons are free to choose from those combination to estimate the sales price.
The problem is, daily sales requires huge amount of data-entry. Within couple of years there may be gigabytes of data (if not terabytes). If I need to show daily, weekly, monthly, quarterly and yearly sales reports there will be various types of sql queries I shall need.
This is my initial design:
Product {ID, Code, Name, IsActive}
ProductXYZPriceHistory {ID, ProductID, Date, EffectDate, Price, IsCurrent}
SalesPerson {ID, Code, Name, JoinDate, and so on..., IsActive}
SalesPersonSalesAraeaHistory {ID, SalesPersonID, SalesAreaID, IsCurrent}
Depot {ID, Code, Name, IsActive}
Outlet {ID, Code, Name, AreaID, IsActive}
AreaHierarchy {ID, Code, Name, PrentID, AreaLevel, IsActive}
DailySales {ID, ProductID, SalesPersonID, OutletID, Date, PriceID, SalesPrice, Discount, etc...}
Now, apart from indexing, how can I normalize my DailySales table to have a fine grained design that I shall not need to change for years to come?
Please show me a sample design of only the DailySales data-entry table (from which all types of reports would be queried) on the basis of above information.
I don't need a detailed design advice. I just need an advice regarding only the DailySales table. Is there any way to break this particular table to achieve granularity?
