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
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, 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
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.
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
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.
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.
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!
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.
Yes, that allows me to delete the join clauses, however why is it making multiple displays in the two circle regions? Josh
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.
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