--
--作成者      :bs-ymizuno
--作成日      :2006/06/05 (月) 11:57:00
--対象DBMS    :PostgreSQL 8.1.4
--

-- 
-- TABLE: TUSER
--

DROP TABLE TUSER;

CREATE TABLE TUSER(
    USER_CODE        CHAR(8)         NOT NULL,
    USER_NAME        VARCHAR(30)     NOT NULL,
    USER_PASSWORD    VARCHAR(16)     NOT NULL,
    USER_DIVISION    CHAR(1)         NOT NULL
)
;


-- 
-- TABLE: ADDRESS 
--
DROP TABLE ADDRESS;

CREATE TABLE ADDRESS(
    CUSTOMER_CODE        CHAR(8)         NOT NULL,
    CUSTOMER_NAME    VARCHAR(30)         NOT NULL,
    POST_CODE    	 CHAR(8)         NOT NULL,
    CUSTOMER_ADDRESS VARCHAR(50)	 NOT NULL,
    CUSTOMER_TEL        CHAR(13)         NOT NULL
)
;


-- 
-- TABLE: TUSER
--

ALTER TABLE TUSER ADD 
    CONSTRAINT FUNC_THIN_PK1 PRIMARY KEY (USER_CODE)
;


-- 
-- TABLE: ADDRESS
--

ALTER TABLE ADDRESS ADD 
    CONSTRAINT FUNC_THIN_PK2 PRIMARY KEY (CUSTOMER_CODE)
;



--
-- TABLE: MEMBER
--
DROP TABLE MEMBER;
CREATE TABLE MEMBER (
MEMBER_CODE CHAR(8) NOT NULL,
MEMBER_NAME VARCHAR(30) NOT NULL,
MEMBER_AGE VARCHAR(3), 
MEMBER_BIRTH CHAR(10));

ALTER TABLE MEMBER ADD 
    CONSTRAINT FUNC_THIN_PK3 PRIMARY KEY (MEMBER_CODE)
;




--
-- TABLE: MESSAGES
--
DROP TABLE MESSAGES;
CREATE TABLE MESSAGES
(
    MESSAGE_KEY VARCHAR(100) NOT NULL,
    MESSAGE_VALUE VARCHAR(100)
);

ALTER TABLE MESSAGES ADD 
    CONSTRAINT FUNC_THIN_PK4 PRIMARY KEY (MESSAGE_KEY)
;

--
-- TABLE: CODELIST
--
DROP TABLE CODELIST;
CREATE TABLE CODELIST
(
    KEY VARCHAR(100) NOT NULL,
    VALUE VARCHAR(100)
);

ALTER TABLE CODELIST ADD 
    CONSTRAINT FUNC_THIN_PK5 PRIMARY KEY (KEY)
;

--
-- TABLE: TUPDOWNLOAD
--
DROP TABLE TUPDOWNLOAD;
CREATE TABLE TUPDOWNLOAD
(
    FILEID                         VARCHAR(20) NOT NULL,
    FILENAME                       VARCHAR(80) NOT NULL,
    FILEPATH                       VARCHAR(200) NOT NULL,
    FILESIZE                       VARCHAR(20) NOT NULL,
    CREATTIME                      DATE,
    UPDATETIME                     DATE,
    UPDATEUSER                     VARCHAR(20)
);

-- 
-- TABLE: DBMESSAGES
--
DROP TABLE DBMESSAGES;
CREATE TABLE DBMESSAGES (
       ID             integer  NOT NULL,
       CODE           VARCHAR(32)  NOT NULL,
       LANGUAGES      VARCHAR(32),
       CONTRY         VARCHAR(32),
       VARIANT        VARCHAR(32),   
       MESSAGE        VARCHAR(256)
);

ALTER TABLE DBMESSAGES ADD 
    CONSTRAINT FUNC_RICH_PK1 PRIMARY KEY (ID)
;


COMMIT;