13 June 2012

Basic Schema for a data base


Creating table user

-- tbl_user :

CREATE TABLE tbl_user (
    pk_bint_user_id BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    vchr_name       VARCHAR(100),
    vchr_email      VARCHAR(50),
    vchr_phone      VARCHAR(50),
    vchr_login_name VARCHAR(50),
    vchr_password   VARCHAR(50),
    chr_gender      CHAR,
    chr_user_type   CHAR COMMENT 'A-Admin, U-User',
    chr_status CHAR COMMENT 'A-Active, B- Block, D-Delete'

);



Creating table files


-- tbl_files :

CREATE TABLE tbl_files (
    pk_bint_file_id BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    fk_bint_user_id BIGINT,
    vchr_file_name       VARCHAR(100),
    vchr_file_size      VARCHAR(50),
    dat_date_time DATETIME,
    vchr_path   VARCHAR(100),
    chr_d_or_f CHAR COMMENT 'F-File, D- Directory',
    chr_file_status CHAR COMMENT 'A-Active, B- Block, D-Delete',
   

    FOREIGN KEY (fk_bint_user_id) REFERENCES tbl_user (pk_bint_user_id)
);



following this syntax is good because we can easily identify which type used for a coulom

No comments:

Post a Comment