Compact Table Design

Tommy1402 · #1 (**permalink**) 12-07-06, 21:57

I have 3 tables with period history field. the Period will be replaced with each end of the month in current year.
so: Period01 = January 31, 2006 and Period12 = December 31, 2006

Here are the fields of each of them:

Vendors (Vendor_ID, Purchase_Period01, Purchase_Period02 , ... , Purchase_Period12,
Payments_Period01, Payments_Period02 , ... , Payments_Period12
)

Items (Item_ID, UnitSold_Period01 , ... , UnitSold_Period12 ,
Sales_Period01 , ... , Sales_Period12 ,
UnitReceived_Period01 , ... , UnitReceived_Period12 ,
Cost_Period01 , ... , Cost_Period12 ,
UnitDamaged_Period01 , ... , UnitDamaged_Period12 ,
Loss_Period01, ... , Loss_Period12
)

Accounts (CoA_ID, Debits_Period01, ... , Debits_Period12 ,
Credits_Period01, ... , Credits_Period12 ,
Activity_Period01, ... , Activity_Period12 ,
Balance_Period01, ... , Balance_Period12
)

My Question is: Do all Tables above is in Normal Form?
If not, can you help me make those Tables in Normal Form?

I would like to compact those Tables' Field design so that those tables consist of as little as possible of Period Fields..?

Thanks for the help..

r937 · #2 (**permalink**) 12-07-06, 22:55

here is a start --

Code:

create table Periods
( period_no  tinyint not null primary key 
, period_enddate  date not null
);   

create table Vendors
( vendor_id integer not null primary key 
, vendor_name varchar(37) not null
);  

create table VendorPurchases
( vendor_id integer not null  
, period_no tinyint not null
, primary key ( vendor_id, period_no )
, purchase_amt  decimal(11,2) not null
);

Tommy1402 · #3 (**permalink**) 12-07-06, 23:04

Well, your solution is the same as everybody else. So, I guess It's the way to go.

Thanks for your help!

Internet Services

Web Development

Digital Marketing

Consumer Tech