[mysql] Sketch of my database

Database : sale

Table : sale_tabaco

日期	貨物	單價	上存貨	結存	銷貨	金額
Date	Name	Price	Upper_stack	Final_stack	Sale	Money

Version 1:

CREATE TABLE sale_tabaco (Sale_date date , Name varchar(20), Price int, Upper_stack int, Final_stack int, Sale int, Money int );

disadvantage:

1.too many column. If I input one or two data into this table, other column will display NULL.

Version 2:

Two table (sale_tabaco_name , sale_tabaco_amount)

1. 
   Create table sale_tabaco_name ( Id INT PRIMARY KEY AUTO_INCREMENT, Name varchar(20), Price int);


2. 
   Create table sale_tabaco_amount ( Sale_date date , Upper_stack int, Final_stack int, Sale int, Money int );

Version 3:

1. 
   version 2
   
   Change sale_tabaco_amount
   
   I want add Name varchar(20) into sale_tabaco_amount
   
   Now sale_tabaco_amount and sale_tabaco_name have relation of each other.


2. 
   Create table sale_tabaco_name ( Id INT PRIMARY KEY AUTO_INCREMENT, Name varchar(20), Price int);


3. 
   Create table sale_tabaco_amount ( Sale_date date ,Name varchar(20), Upper_stack int, Final_stack int, Sale int, Money int );

Version 4: (2011_08_06)

• 
  

    CREATE TABLE total_calc(
    Goods_name varchar(20),
    Unit_price int,
    Stocks int,
    Good_in_stock int,
    Ship int,
    Balance int,
    Selling_goods int,
    Subtotal int,
    Space_reserve varchar(100),  Date Date
  );    
  Goods_name 貨名，Unit_price 單價，Stocks 庫存，Good_in_stock 上存貨，Ship 進貨，Balance 結存，
  Selling_goods 銷貨，Subtotal 小計，Space_reserve 備註
  

Version 5: (2011_08_07)

• 
  

    CREATE TABLE total_calc(
    Shift varchar(100) CHARACTER SET BIG5,
    Goods_name varchar(20) CHARACTER SET BIG5,
    Unit_price int,
    Stocks int,
    Good_in_stock int,
    Ship int,
    Balance int,
    Selling_goods int,
    Subtotal int,
    Space_reserve varchar(100) CHARACTER SET BIG5,  Date Date
  );    
  Shift 班表,Goods_name 貨名，Unit_price 單價，Stocks 庫存，Good_in_stock 上存貨，Ship 進貨，Balance 結存，
  Selling_goods 銷貨，Subtotal 小計，Space_reserve 備註 Date 日期
  新增一個班表，來表示這班是誰做的。

Version 5 - 1 : (2011_08_09)

CREATE TABLE tabaco_product(
  Tabaco_order int,
  Tabaco_name varchar(20),
  Tabaco_unit_price int
);

新增一個 tabaco 產品的資料表。

Version 5 - 2 : (2011_08_09)

CREATE TABLE beverage_product(
  Beverage_order int,
  Beverage_name varchar(20),
  Beverage_unit_price int
);

新增一個 beverage 產品的資料表。

Reference:

• http://zetcode.com/databases/mysqljavatutorial/
• the data of JTable put into database(mysql)