1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Linking Some Tables

Discussion in 'Databases' started by JoshuaEir, May 29, 2020.

0
  1. #1
    I have three tables :

    Product
    ---------
    Product ID key
    image
    Description

    Order Item
    ------------
    Order ID key
    Product ID fkey
    Quantity


    Saved Orders (a record of bought transactions)
    ---------------
    SavedOrderID key
    Name
    Date
    Product ID fkey
    Quantity

    The order item reduces its quantity when an order is made. If all of the item is bought than the order item record is deleted.

    My question are:

    1. Does the link and columns look okay for the three tables?
    2. Should Saved Orders be a separate database?
    3. If there is some database where a table such as Customers can link to more than one column what is done?

    Thanks a ton,
    Joshua
     
    Solved! View solution.
    JoshuaEir, May 29, 2020 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #2
    Hi Joshua

    I'll start with some terminology

    A database is just a bucket that holds all your tables - for most of us we have a single database holding all the information for our entire website.

    SavedOrders doesn't need a product key - that's held in the OrderItem table.

    I created a SQLFiddle of the bare bones of an ordering system at http://sqlfiddle.com/#!9/d2c1f/2 it will let you have a mess about with columns in each table and running queries etc.

    Just in case the fiddle disappears here's the code
    CREATE TABLE `products` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `image` varchar(150) NULL,
  `name` varchar(150) NOT NULL,
  `description` text NULL,
  PRIMARY KEY (`product_id`)
);

CREATE TABLE `orderitems` (
  `orderitem_id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity` int(5) NOT NULL,
  PRIMARY KEY (`orderitem_id`)
);

CREATE TABLE `orders` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `orderdate` datetime NOT NULL,
  `ponumber` varchar(15) NULL,
  PRIMARY KEY (`order_id`)
);

CREATE TABLE `customers` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `tradingas` varchar(150) NOT NULL,
  `address1` varchar(100) NULL,
  `address2` varchar(100) NULL,
  `address3` varchar(100) NULL,
  `postcode` varchar(10) NULL,
  `country` varchar(100) NULL,
  PRIMARY KEY (`customer_id`)
);

CREATE TABLE `contacts` (
  `contact_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `firstname` varchar(50) NOT NULL,
  `lastname` varchar(50) NULL,
  `email` varchar(100) NULL,
  PRIMARY KEY (`contact_id`)
);

INSERT INTO `customers` values 
(1,'Acme Ltd','45 Willow Lane','','','','USA'),
(2,'WidgetsRUs', '32 Maple Row', '','','','USA');

INSERT INTO `contacts` values
(1, 1, 'John','Smith','john@acme.com'),
(2, 1, 'Sally', 'Brown', 'sally@acme.com');

INSERT INTO `products` VALUES
(1, '', 'AL123',''),
(2, '', 'AZ654','');

INSERT INTO `orders` VALUES 
(1, 1, NOW(), '231');

INSERT INTO `orderitems` VALUES 
(1, 1, 1, 10),
(2, 1, 2, 3);
    Code (markup):
    select customers.tradingas, orders.order_id, products.name, orderitems.quantity
from customers 
left join orders on (orders.customer_id = customers.customer_id)
left join orderitems on (orderitems.order_id = orders.order_id)
left join products on (products.product_id = orderitems.product_id);
    Code (markup):
     
    sarahk, May 29, 2020 IP
  3. JoshuaEir

    JoshuaEir Member

    Messages:
    59
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    28
    #3
    SarahK, so grateful. I look forward to trying the SQL Fiddle. Totally cool. You said, "SavedOrders doesn't need a product key - that's held in the OrderItem table." I am thinking since the orderitem table is deleted when the purchases reduce it to zero I needed this. Yes, No?

    Josh
     
    JoshuaEir, May 29, 2020 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #4
    Why would you delete it?

    If you picture a docket/receipt from a store you have loads of items on it, that's what your order is. It starts as a list of things you want to buy but haven't paid for, and once you pay the receipt's status changes from unpaid to paid.

    In a business an order might even start as "new", change to "paid", move to "pending fulfilment", "pending shipping", and finally "complete". Every company will have a slightly different process.

    You need to keep all the order items though so that you can answer any queries your customer might have about their order, including returns.
    The warehouse people need to be able to see any orders stuck at "pending fulfilment" or maybe "backorder" for stock that they need to buy in.
    Each month the bosses are going to want pretty charts showing which products sold the best, which customers were the most profitable, which sales reps get the bonuses (the structure I've used hasn't included links to sales reps, or to sales regions)

    If your company has a big problem with backorders you might even have a status per order item - you might be able to ship widget A straight away, but have to wait a while for widget B to be made before you can ship it out.
     
    sarahk, May 29, 2020 IP
  5. JEET

    JEET Notable Member

    Messages:
    3,825
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #5
    I don't see the point of having "order item" table to begin with.
    Its only storing quantity, nothing else.
    Think about this structure:

    PRODUCTS table:
    productID (primary auto increment key)
    image (varchar)
    description (text)
    quantity (int not null)


    ORDERS table
    orderID (primary)
    productID
    customerID
    quantity_ordered
    order_date (timestamp)

    CUSTOMER table
    customerID (primary)
    customerEmail
    customerPassword
    customerName
    etc etc


    When a product is ordered, simply subtract from the "quantity" in the PRODUCTS table.
    If quantity is already zero, do not allow to place orders for that product
     
    JEET, May 29, 2020 IP
  6. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #6
    What about when someone buys more than one type of item?
    How do you print them an invoice?
     
    sarahk, May 29, 2020 IP
    JEET likes this.
  7. #7
    My point is, why is "quantity" in a separate table, and not in the products table itself.

    His original table structure also does not supports invoice printing.
    orderItem table is not storing "savedOrderID", and savedOrder table is not storing orderID from orderItem table.
    I don't see how he can link those 2 together...

    To print invoices, he needs to add a column to orders table and store an InvoiceNumber in that column.

    ORDERS table
    orderID (primary)
    invoiceID
    productID
    customerID
    quantity_ordered
    order_date (timestamp)


    InvoiceID can simply be a random string, or number (depends on his column type), uniquely assigned to shopping cart.
    Now all the orderID having same invoiceID are part of 1 single bigger order.
    Then he can print invoices.

    $invoiceID='123456';

    select o.orderID, p.productName from orderTable as o, productTable as p where
    o.productID = p.productID and
    o.invoiceID='$invoiceID' ";

    Same can be run as JOIN

    select o.orderID, p.productID (etc)
    from orderTable as o
    left join productTable as p on
    o.productID = p.productID
    where
    o.invoiceID='$invoiceID'

    Resultset will have all orderID and productID (name etc) for that invoice.
     
    JEET, May 30, 2020 IP
  8. JoshuaEir

    JoshuaEir Member

    Messages:
    59
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    28
    #8
    Selected query records are displayed with purchase buttons, they have quantity.
    when purchased the records id is stored in an array and the quantity in another array whose index is the id. When the different items are purchased they are deleted or reduced from the product database and an order record is put in the database for future use. Also an email is sent for their records as well.
     
    JoshuaEir, May 30, 2020 IP
  9. JoshuaEir

    JoshuaEir Member

    Messages:
    59
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    28
    #9
    Thanks Jeet!
     
    JoshuaEir, May 30, 2020 IP
    JEET likes this.
  10. JoshuaEir

    JoshuaEir Member

    Messages:
    59
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    28
    #10
    May I have help please? I tried to use SQLFiddle but I don't understand the circled values. When I try to simplify the SQL, I get: Error is: unknown column 'products.name' in 'fields list'.

    Thanks!





    sqlfiddle.gif
     
    Last edited: May 31, 2020
    JoshuaEir, May 31, 2020 IP
  11. JEET

    JEET Notable Member

    Messages:
    3,825
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #11
    This is because your products table probably looks like this:
    PRODUCTS table:
    productID (primary auto increment key)
    image (varchar)
    description (text)
    quantity (int not null)




    Make it look like this:

    PRODUCTS table:
    productID (primary auto increment key)
    productName varchar(256)
    image (varchar)
    description (text)
    quantity (int not null)

    I don't use sqlfiddle site, @sarahk can tell better about it. She is very familiar with it.
     
    JEET, May 31, 2020 IP
  12. JoshuaEir

    JoshuaEir Member

    Messages:
    59
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    28
    #12
    Yes, that allows me to delete the join clauses, however why is it making multiple displays in the two circle regions?

    Josh
     
    JoshuaEir, May 31, 2020 IP
  13. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #13
    If you read the query I asked for all the order items, their orders and their customer names. Because Acme had ordered two different items they appear on the list once, but with the same order number. Take some time to learn the basics of sql - is this a real project or part of a course you're doing? The reason I ask is because if this is going to grow into a real inventory and order system there are some things you need to build in. If it's just a demonstration of data normalisation, data saving and retrieval then we don't need to go into so much detail.
     
    sarahk, May 31, 2020 IP
    JEET likes this.
  14. JoshuaEir

    JoshuaEir Member

    Messages:
    59
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    28
    #14
    Well I had been programming with C++ and needed to get some experience on my own, so this is one of the projects I chose. I am using PHP for the backend. The point is to get hired, starting with an internship. From my research I have found that the actual language is less important than the process of getting it done. I studied SQL a bit in the early nineties and as far as learning the basics I only have an hour spent on joins. However, the SQLFiddle was a real example which is new. It also has multiple joins. I will look at it again soon, I have been pretty busy. As far as your insight, I would love to here anything about what you mentioned or anything really.

    Thanks, Josh
     
    JoshuaEir, Jun 1, 2020 IP