SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2010
    Posts
    99
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to get 3NF in this?

    Hi

    I've been wondering how to do this system of tables in MySQL to 3NF.
    I don't see how this is going to look like, I generate orders in PHP and then insert these to database order, but how should I do this?
    And how do I insert data to these actually, somethin different or the ordinary way?

    I need following tables:
    products
    • id
    • item
    • price


    clients
    • id
    • first_name
    • last_name
    • email


    order
    • id
    • quantity
    • product


    Thank you

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    change the order table ...

    orders
    • id
    • client_id
    • order_date
    • total_price

    order_items
    • order_id
    • product_id
    • quantity
    • price_at_time_of_order
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Does your order only contain one product?
    And how do you understand who made the order?

    I'd start with changing the order table a bit, and add a new table orderlines (or orderproducts or whatever):

    order
    * id
    * client
    * orderdate
    * whatever else you want to know about the order

    order_lines
    * id
    * order
    * product
    * quantity

  4. #4
    SitePoint Enthusiast
    Join Date
    Feb 2010
    Posts
    99
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I need the order to contain more then 1 product.

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    So implement r937's or my modifications (they're almost identical )
    That way an order can contain more products

  6. #6
    SitePoint Enthusiast
    Join Date
    Feb 2010
    Posts
    99
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the replies, I constructed these SQL statements.
    Is this the most or more efficient way of doing this kind of tables, from database point of view? Or could it be done more efficiently?

    Code MySQL:
    CREATE TABLE products
    (
    product_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    product VARCHAR(70) NOT NULL,
    price DECIMAL(5,2) NOT NULL
    )
     
    CREATE TABLE clients
    (
    client_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    address VARCHAR(150)
    )
     
    CREATE TABLE orders
    (
    order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    sum DECIMAL(5,2),
    FOREIGN KEY (client_id) REFERENCES clients(client_id)
    )
     
    CREATE TABLE order_items
    (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (tellimus_id) REFERENCES orders(order_id),
    FOREIGN KEY (kaup_id) REFERENCES products(product_id)
    )

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    add a primary key to the order_items table: PRIMARY KEY (order_id,product_id)

    yes, what you have is the most efficient way of doing customers/orders/items
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Feb 2010
    Posts
    99
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, sorry, I made a mistake in the order_items table.
    Does this still need primary key? Because they are foreign keys now.
    The order_items table has two foreign keys, does this eliminate the need for primary key, as they together make up a composite key? Or is there some SQL statement for creating Composite key?

    Code MySQL:
    CREATE TABLE order_items
    (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
    )

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    CREATE TABLE order_items
    ( order_id INT NOT NULL
    , product_id INT NOT NULL
    , quantity INT NOT NULL
    , FOREIGN KEY (order_id) REFERENCES orders(order_id)
    , FOREIGN KEY (product_id) REFERENCES products(product_id)
    , PRIMARY KEY (order_id,product_id)
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast
    Join Date
    Feb 2010
    Posts
    99
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have come to another problem, inserting data to the tables.

    I have constructed these querys, in PHP.

    PHP Code:
    $query1 "INSERT INTO clients (first_name, last_name, address) VALUES ('$firstname', '$lastname', '$address')";
    mysqli_query($dbc$query1) or die('MySQL Error clients: '.mysqli_error($dbc).' ('.mysqli_errno($dbc).')');

    $query2 "SELECT client_id FROM clients WHERE first_name = '$firstname' AND last_name = '$lastname' LIMIT 1";
    $result mysqli_query($dbc$query2) or die('MySQL Error client_id: '.mysqli_error($dbc).' ('.mysqli_errno($dbc).')');
    $client_id mysqli_fetch_row($result);

    $query3 "INSERT INTO orders (client_id, sum) VALUES ('$client_id', '".$_SESSION['totalsum']."')";
    mysqli_query($dbc$query3) or die('MySQL Error orders: '.mysqli_error($dbc).' ('.mysqli_errno($dbc).')');

    $query4 "SELECT order_id FROM orders WHERE client_id = '$client_id' LIMIT 1";
    $result1 mysqli_query($dbc$query4) or die('MySQL Error order_id: '.mysqli_error($dbc).' ('.mysqli_errno($dbc).')');
    $order_id mysqli_fetch_row($result1);

    for (
    $i 0$counter count($_SESSION['cart']); $i <= $counter$i++) {
    $query5 "INSERT INTO order_items (order_id, product_id, quantity) VALUES ('".$order_id."', '".$_SESSION['cart'][$i][4]."', '".$_SESSION['cart'][$i][3]."')";
    mysqli_query($dbc$query5) or die('MySQL Error order_item: '.mysqli_error($dbc).' ('.mysqli_errno($dbc).')');

    In the query5 it gives me this error: Duplicate entry '0-0' for key 'PRIMARY'.
    I understand that a primary key cannot be duplicate, but how should I enter the data to it?

    Can this be done more easily? With subquerys maybe?

    If someone is still listening to this topic, then please help me out.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    it looks like the values for the order_id and product_id are both 0

    by the way, query4 is faulty, you are retrieving a random order for the customer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Enthusiast
    Join Date
    Feb 2010
    Posts
    99
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, I thought I got the flow of this model.
    I'm tired of thinking about this

    How exactly is query4 faulty?
    I thought that the every client_id had a different order_id to them in the orders table. My assumption is wrong then, but how?

    Thank you

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    can a client have more than one order?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Enthusiast
    Join Date
    Feb 2010
    Posts
    99
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Haha,
    that is true.

    I'll add a date column to the table.


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
  •