Can not understand MySQL tables relations

[somed3v3loper]

Hi all ,

As this is not related to B4X , I posted it chit chat .

I am trying to make a shop application where customers can buy some products .
The problem is that I do not know how to make orders table .
As the customer can purchase more than one product and of different quantities , orders table should be a special table with relations with products table which I can't understand .

Can someone help me in this case (preferably with a simple example)?

Thanks in advance

 

[udg]

Probably at a minimum you'll want to have four tables: Customers, Products, Orders, OrderDetail.
Customers holds your customers' data; Products holds data for each product in your catalog; Orders holds the general data about each order (customer, shipping address, date of order..etc); OrdersDetail holds reference to products belonging to any order.
The general concept is that a table, among its fields, keeps a "pointer" to a field belonging to another table with which it should establish a relation. This pointer is known as "foreign key".

In the example above, assuming that each table has a first field named "id", the Orders table will have, among the others, a field you may name customers_id where you store the id of the customer sending the order.
Same for OrderDetails; here you will have two FKs, one for the Order and one for the Product.

Once you have things set up this way, it will be easy to search for all the orders from a specific customer or to prepare an invoice listing all the products making up a specific order.

Sorry to not be able to prepare an example but I'm on a tablet now.

Have a nice day.

ps: you may find useful this short introduction to SQL

 

[inakigarm]

Schematically: (not tested at all!)

- Product Table (Product_Id, Name, description,colour,price)
- Customer Table (Customer_Id, Name, address,e-mail, etc..)
- Discount Table Customer (DiscCust_Id, Customer_id,start discount time, end discount time)
- Discount Table Product (DiscProd_Id, Product_id,start discount time, end discount time)
- Order Table (Order_Id, Product_id,Customer_id,Quantity,Price,Buy_Date)
- Delivery Table (Deliver_Id, Order_Id, Product_id,Customer_id,Order_table.Quantity,etc...)

When querying, inserting, updating, deleting table, you'll have to Join the tables (look at http://www.sql-join.com/sql-join-types) and write WHERE clauses

SELECT Order_Id,Customer.Name,Product.Name,Product.colour from Order
(LEFT/INNER/RIGHT) JOIN Product ON Product.Product_Id = Order.Product_id
(LEFT/INNER/RIGHT) JOIN Customer ON Customer.Customer_Id = Order.Customer_id
WHERE ORDER.Buy_Date < "12/12/2016"

 

[KMatle]

Think of it as objects (=classes):

A person/customer is an independent object. An item, too. Even a order (think of it as a bill). As a sentence: A person can order items.

Each object must have a unique id. Like Michal Smith is not unique enough (which one of the 1000 was it?). So spend every object an unique id. This is often called customer-Id/item-id, etc. Then it is easy to build relations like: The customer with id 1234 has done an order with id 4567 with the items 6, 8, 9 and 21. So everything is unique and easy to handle.

The order's table puts everything together (see inakigam's post).