1

I'm trying to create a database for a restaurant ordering portal, I've created the tables excluding the payment table. I'm a bit stuck on whether its the correct structure. If i could get any feedback it would be greatly appreciated! so the database should allow the following:

  • allow the customer to order
  • restaurant can update/add to menu
  • restaurant can confirm/deny an order

(i havent added Pkeys or Fkeys yet)

CREATE TABLE Customer (
customerID INT(11) NOT NULL,
Fname VARCHAR(100) NOT NULL,
Lname VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL,
phone INT(14) NOT NULL,
address VARCHAR(255) NOT NULL,
postcode VARCHAR(20) NOT NULL

CREATE TABLE CusOrder (
orderID INT(11) NOT NULL,
prodname VARCHAR(55) NOT NULL,
quantity INT(11) NOT NULL,
price FLOAT(10 , 2 ) NOT NULL,
totalprice FLOAT(10 , 2 ) NOT NULL,
menuID INT(11) NOT NULL,
deliveryfee FLOAT(10 , 2 ) NOT NULL,
customerID INT(11) NOT NULL

CREATE TABLE resturantMenu (
menuID INT(11) NOT NULL,
deliveryfee FLOAT(10 , 2 ) NOT NULL,
productName VARCHAR(255) NOT NULL,
productPrice FLOAT(10 , 2 ) NOT NULL

CREATE TABLE resturantLogin (
resturantUserID INT(11) NOT NULL,
menuID INT(11) NOT NULL,
username VARCHAR(255) NOT NULL,
pass VARCHAR(255) NOT NULL,
restDetails VARCHAR(255) NOT NULL,
restPhone INT(14) NOT NULL,
restAddress VARCHAR(255) NOT NULL,
restPostcode VARCHAR(20) NOT NULL

CREATE TABLE orderConfirm (
orderID INT(11) NOT NULL,
resturantUserID INT(11) NOT NULL,
status ENUM('yes','no') NOT NULL

not afraid of criticism so please fire away :)

3
  • When you like to receive a code review please try codereview.stackexchange.com
    – Amacado
    Jul 17, 2020 at 17:02
  • ACtually it belongs to dba
    – nbk
    Jul 17, 2020 at 17:08
  • Use DECIMAL(10,2), not FLOAT. Without the SELECTs, we can't help you with the indexes, which will be important.
    – Rick James
    Jul 17, 2020 at 21:51

1 Answer 1

0

First: don't code, draw. It's much easier to make mistakes in diagrams than it is to try and visualize relations, keys, etc. in code. At this point in design column types are irrelevant. Focus on keys and relations.

This may be a good starting point*: enter image description here

Here we have customers who can place orders for restaurants. Those orders cannot contain items not offered by that restaurant. Those orders can include the same item at different prices (buy 1, get 1 50% off, etc). Those orders can include fees, including delivery fees which can be looked up from a schedule.

Questions not answered by this diagram:

  1. How do we determine if a OrderLine is a menu item or a charge and enforce that?
  2. How is the status of an Order defined?
  3. What exactly encompasses OrderLineCharge?
  4. Should the order request a menu item/quantity which is then becomes an OrderLine?
  5. When an MenuItem is requested but can't be fulfilled, what happens?
  6. How are price changes stored?
  7. Should we track types of cuisine or categories of menu items?
  8. How is delivery tracked?
  9. How are payments tracked?
  10. How do we track quantity discounts ($2 each, 3 for $5, etc)?
  11. Should it be 'OrderId' or 'OrderNumber'

That is a lot of questions we won't solve with lines of code or table definitions, at least not yet.

The next steps I'd recommend are:

  1. Familiarize yourself with ERDs, specifically IDEF1X which are easily read and understood
  2. Start thinking about business rules and how they might shape your data model - what problems can you solve through constraints/relations? Which problems will require the transactions to contain rules?

*I'm leaving a lot out because this isn't something that can be answered in one step - I don't know if this is a homework assignment, hobby project, or actual work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.