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

Haha:),
that is true.

I’ll add a date column to the table.

can a client have more than one order?

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

I have come to another problem, inserting data to the tables.

I have constructed these querys, in PHP.


$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.

Hmm, I thought I got the flow of this model.
I’m tired of thinking about this:lol:

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

So implement r937’s or my modifications (they’re almost identical :wink: )
That way an order can contain more products

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)
);

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?


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)
)

I need the order to contain more then 1 product.

change the order table …

orders[list]
[]id
[
]client_id
[]order_date
[
]total_price[/list]
order_items[list]
[]order_id
[
]product_id
[]quantity
[
]price_at_time_of_order[/list]

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

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

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?


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)
)