Design of a Database

Question

Design of a Database

Basit Ali Khan

SSC Veteran

Points: 232
More actions
April 16, 2005 at 3:22 am

#166579

Hello Friends!
I design a database for a company which sale their products. The company has their outlets in four regions i.e. (North, South, East and WEST) and they want to check their sale of the Product.. The sample report’s format and data are shown below:
1.Product-Wise Monthly Sale
Sale for the Month of July-2004
BooksNoteBooksPenPencils
Schools25105025
Colleges155103
Universities630143
Government22761
Others2132548
TOTAL:6910413440
2.Sector-Wise Annual Sale
Sale for the Year 2004-05
BooksNoteBooksPenPencils
Schools25105025
Colleges155103
Universities630143
Government22761
Others2132548
TOTAL:6910413440
3.Sale of Product for the current and previous years
Sale by Product
2001-022002-032003-042004-05
Books1051525
NoteBooks1051525
Pens1051525
Pencils1051525
TOTAL:402060100
3.Sale by Sector for the current and previous years
Sale by Sector
2001-022002-032003-042004-05
Schools1051525
Colleges1051525
Universities1051525
Government1051525
Others402060100
TOTAL:8040120200
4.Region-Wise Sale for the current and previous years.
Sale by Region
2001-022002-032003-042004-05
East1051525
West1051525
North1051525
South1051525
TOTAL:402060100
With the help of above reports I design the following database. Please let me tell whether the following database design is ok or not otherwise tell me how to improve it.
Year (Table)
YearID int
Description Varchar2
Period_From Date
Period_To Date
Current logical
Month (Table)
MonthID int (1,2,3,4,5,6,7,8,9,10,11,12)
MonthDescription varchar2 (Jan,Feb, Mar, Apr, May, Aug, Sept, Aug, Oct, Nov, Dec)
StartDate Date
EndDate Date
Region(Table)
RegionID int (1,2,3,4)
RegionName varchar2 (East, West, North, South)
Sector (Table)
SectorID int (1,2,3,4,5)
Sector Varchar2 (School, Colleges, University, Government, Others)
Product (Table)
ProductID int (1,2,3,4)
ProdName varchar2 (Books, NoteBooks, Pen, Pencil)
Sale (Table)
SaleID int
ProductID int
Product varchar2
Sale int
RegionId int
SectorID int
MonthID int
YearID int
Thanks.
(BASIT)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

michanne SSCrazy Points: 2329 More actions · Answer 1

there is no reason to have month and yr tables. these values can be calculated other ways. Simply have a date column in the sale table. things like 'sale by year' are dynamic. dates can be broken down with built in functions like month() or convert.

region could also be eliminated - you could use a char(1) in your sale table and abbreviate your regions (E,W,S,N).

a similar solution may be applied to replace the other lookups but the decision to do that has a lot to do with the amount of data you will be collecting and personal style.

good luck!

my 2 cents

Basit Ali Khan SSC Veteran Points: 232 More actions · Answer 2

Thank you for the your detailed review and reply. As per your suggestion, I dropped the tables month and year and add a DATE column in the sale table I think you are right but one thing confusing me that how could we get the report of previous five years sale of the products.

Regards,

(BASIT)

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 3

select datepart(yy, date) as year, datepart(mm, date) as month, *
from yourtable
where datepart(YY, date) = '2004'
order by datepart(yy, date), datepart(mm, date)

I've broken it down into month too so you can see how it works, obviously this is extremely simplified but you should be able to adapt it to do what you want.