Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2006
    Posts
    2

    Unanswered: Compact Table Design

    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..

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2006
    Posts
    2
    Well, your solution is the same as everybody else. So, I guess It's the way to go.

    Thanks for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •