SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is this schema looking okay?

    Hi Guys

    Ive created my model and interpreted it into a schema. I will be using Mysql.

    Does it make sense?

    Model:


    Schema:
    CREATE TABLE tblcustomer (
    login_name VARCHAR(20) NOT NULL,
    createDate DATE NOT NULL,
    password VARCHAR(255) NOT NULL,
    lastName VARCHAR(50) NULL,
    firstName VARCHAR(50) NULL,
    street VARCHAR(50) NULL,
    town VARCHAR(50) NULL,
    postcode CHAR(10) NULL,
    email VARCHAR(50) NULL,
    phone CHAR(15) NULL,
    fax CHAR(15) NULL,
    CONSTRAINT customer_key PRIMARY KEY (‘login_name’)
    );

    CREATE TABLE tbllogin (
    login_time DATETIME NOT NULL,
    login_name VARCHAR(20) NOT NULL,
    CONSTRAINT login_key PRIMARY KEY (‘login_time’, ‘login_name’)
    );

    CREATE TABLE `tblproducts` (
    `product_id` int(5) NOT NULL auto_increment,
    `product_type_description` varchar(255) NOT NULL default '',
    `product_name` varchar(50) NOT NULL default '',
    `product_description` varchar(255) NOT NULL default 'no description at this time',
    `product_price` decimal(9,2) NOT NULL default '0.00',
    `product_image` varchar(15) NOT NULL default 'na.gif',
    CONSTRAINT products_key PRIMARY KEY (‘product_id’),
    CONSTRAINT holds FOREIGN KEY (‘product_type_description’) REFERENCES tblproduct_types (`product_type_description`)
    );

    CREATE TABLE tblproduct_types (
    product_type_description VARCHAR(255) NOT NULL,
    product_type_image CHAR(15) NULL,
    CONSTRAINT productypes_key PRIMARY KEY (‘product_type_description’)
    );

    CREATE TABLE tblcustomer_product_interest (
    login_name VARCHAR(20) NOT NULL,
    product_type_description VARCHAR(255) NOT NULL,
    CONSTRAINT produduct_interests_key PRIMARY KEY (‘login_name’, ‘product_type_description’)
    );

    CREATE TABLE tblcustomer_product_inspection(
    datetime_inspect DATETIME NOT NULL,
    login_name VARCHAR(20) NOT NULL,
    product_id INT(5) NOT NULL auto_increment,
    CONSTRAINT product_inspection_key PRIMARY KEY (‘datetime_inspect’,’ login_name’,’ product_id’)
    );

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    696
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Don't crosspost.


Bookmarks

Posting Permissions

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