-- GOODS
DROP TABLE goods;
CREATE TABLE goods (
    goods_cd CHAR(6) NOT NULL
    , goods_name VARCHAR(40) NOT NULL
    , price INT DEFAULT 0 NOT NULL
    , calorry INT DEFAULT 0
    , sales_start date DEFAULT current date
    , manual_text VARCHAR(1024)
    , CONSTRAINT pkey_goods PRIMARY KEY (goods_cd)
);

-- RECIPE
DROP TABLE recipe;
CREATE TABLE recipe (
    goods_cd CHAR(6) NOT NULL
    , food_cd CHAR(6) NOT NULL
    , necessary_num NUMERIC(7, 2) DEFAULT 1 NOT NULL
    , CONSTRAINT pkey_recipe PRIMARY KEY (goods_cd, food_cd)
);

-- FOOD
DROP TABLE food;
CREATE TABLE food (
    food_cd CHAR(6) NOT NULL
    , food_name VARCHAR(40) NOT NULL
    , supplier_cd CHAR(6) NOT NULL
    , supplier_cost INT DEFAULT 0 NOT NULL
    , supplier_unit VARCHAR(10) NOT NULL
    , convert_cost INT DEFAULT 1 NOT NULL
    , recipe_unit VARCHAR(10) NOT NULL
    , CONSTRAINT pkey_food PRIMARY KEY (food_cd)
);

-- SUPPLIER
DROP TABLE supplier;
CREATE TABLE supplier (
    supplier_cd CHAR(6) NOT NULL
    , supplier_name VARCHAR(40) NOT NULL
    , addr VARCHAR(60)
    , post_num VARCHAR(8)
    , tel VARCHAR(20)
    , fax VARCHAR(20)
    , CONSTRAINT pkey_supplier PRIMARY KEY (supplier_cd)
);

