SitePoint Sponsor

User Tag List

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

    Is my schema 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
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    ditch the ‘microsoft word curly quotes’

    since this is for a school project, you will probably want to define all your foreign keys, even though mysql doesn't support them (it will still accept and parse the syntax for them)

    you may also want to reconsider the use of the "tbl" prefix in your table names -- it's redundant and irritating

    after all, you don't say --

    CREATE TABLE tblcustomer (
    columnlogin_name VARCHAR(20) NOT NULL,
    columncreateDate DATE NOT NULL,
    columnpassword VARCHAR(255) NOT NULL,
    columnlastName VARCHAR(50) NULL,
    columnfirstName VARCHAR(50) NULL,
    columnstreet VARCHAR(50) NULL,
    columntown VARCHAR(50) NULL,
    columnpostcode CHAR(10) NULL,
    columnemail VARCHAR(50) NULL,
    columnphone CHAR(15) NULL,
    columnfax CHAR(15) NULL,
    CONSTRAINT constraint_customer_key PRIMARY KEY (columnlogin_name)
    );
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    since this is for a school project, you will probably want to define all your foreign keys, even though mysql doesn't support them (it will still accept and parse the syntax for them)
    Can you give me an example of what you mean here?

    Cheers

    Best Wishes

    Richard

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    okay, here is an example

    you have defined the FK product_type_description in the tblproducts table

    you have not defined the FK login_name in the tblcustomer_product_interest table
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    okay, here is an example

    you have defined the FK product_type_description in the tblproducts table

    you have not defined the FK login_name in the tblcustomer_product_interest table
    Ammended Schema:

    CREATE TABLE customer (
    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 login (
    login_time DATETIME NOT NULL,
    login_name VARCHAR(20) NOT NULL,
    CONSTRAINT login_key PRIMARY KEY (login_time, login_name),
    CONSTRAINT holds FOREIGN KEY (login_name) REFERENCES customer (login_name)
    );

    CREATE TABLE products (
    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 product_types (product_type_description)
    );

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

    CREATE TABLE customer_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),
    CONSTRAINT holds FOREIGN KEY (login_name) REFERENCES customer (login_name),
    CONSTRAINT holds FOREIGN KEY (product_type_description) REFERENCES product_types (product_type_description)
    );

    CREATE TABLE customer_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),
    CONSTRAINT holds FOREIGN KEY (login_name) REFERENCES customer (login_name),
    CONSTRAINT holds FOREIGN KEY (product_id) REFERENCES products (product_id)
    );

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you may run into difficulty with all those identically-named constraints

    yes, i realize it's probably a result of hasty cuttin' and pastin'

    but you must really slow down and try to do this on your own

    are you testing any of this in mysql as you go along?

    surely you do not want to rely on us for absolutely everything
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    you may run into difficulty with all those identically-named constraints

    yes, i realize it's probably a result of hasty cuttin' and pastin'

    but you must really slow down and try to do this on your own

    are you testing any of this in mysql as you go along?

    surely you do not want to rely on us for absolutely everything

    Do you mean for example.

    CONSTRAINT holds FOREIGN KEY (product_id) REFERENCES products (product_id)
    Here I am using 'holds'.

    Is this what you mean?

    Yes i am running it through mysql as i go along

  8. #8
    SitePoint Member
    Join Date
    Jan 2004
    Location
    London, Ontario
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You should have a customer_id field and use it as your primary key in the customer table (and foreign key in the other tables). Its always quicker for look up and sorting if you are using an number field then a text field. Also, a customer may want to change their login_name (which is perfectly resonable) and you wouldn't be able to with how its setup now. Well you could, but it would require massive data changes to occur and thats not a good way to operate a db.

  9. #9
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Rivux
    You should have a customer_id field and use it as your primary key in the customer table (and foreign key in the other tables). Its always quicker for look up and sorting if you are using an number field then a text field. Also, a customer may want to change their login_name (which is perfectly resonable) and you wouldn't be able to with how its setup now. Well you could, but it would require massive data changes to occur and thats not a good way to operate a db.

    do you think i should keep the login name as a primary key and have a customer id as a primary key also?

    i am using mysql

    cheers

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you can have only one primary key per table
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    teesside
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    you can have only one primary key per table
    hi rudy

    my schema states otherwise.

  12. #12
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by weewizard
    hi rudy

    my schema states otherwise.
    No, your schema states a composite primary key. It is only one key that comprises of multiple pieces, in this case the login name and the customer id.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    thanks stephan

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •