Click here to Skip to main content
14,641,343 members
Rate this:
Please Sign up or sign in to vote.
Hi yall! This is a small database for MySQL. My company is a bakery store where I have to develop a simple database for them. I have to start from the beginning. Right now I have set up my ER diagrams and Business rules. But somehow it makes me feel like this database is weird. So can you guys have any recommend for me.
I am using MySQL Workbench. I have created 5 tables PASTRY, ORDER, ORDERItem, CUSTOMER and EMPLOYEE. (ORDERItem is a linking table). I have run my script and it worked perfectly fine. FYI, I'm new to Database.


Do you guys have any suggestions! Thank you so much!

What I have tried:

/* my project*/
BEGIN;

DROP TABLE IF EXISTS PASTRY;
DROP TABLE IF EXISTS CUSTOMER;
DROP TABLE IF EXISTS EMPLOYEE;
DROP TABLE IF EXISTS ORDERs;
DROP TABLE IF EXISTS ORDERItem;


CREATE TABLE PASTRY (
BAKEID INTEGER NOT NULL,
BAKE_CATEGORY VARCHAR(20) NOT NULL,
BAKE_PRICE NUMERIC (8,2) NOT NULL,
BAKE_QOH SMALLINT NOT NULL,
PRIMARY KEY (BAKEID));

CREATE TABLE CUSTOMER (
CUS_ID NUMERIC PRIMARY KEY,
CUS_FNAME VARCHAR(15) NOT NULL,
CUS_LNAME VARCHAR(15) NOT NULL,
CUS_PHONE CHAR(12) NOT NULL,
CUS_BALANCE NUMERIC(9,2) DEFAULT 0.00,
CONSTRAINT CUS_UI1 UNIQUE (CUS_FNAME, CUS_LNAME));

CREATE TABLE EMPLOYEE (
EMP_ID NUMERIC PRIMARY KEY,
EMP_FNAME VARCHAR(15) NOT NULL,
EMP_LNAME VARCHAR(15) NOT NULL,
EMP_PHONE CHAR(12));

CREATE TABLE ORDERs (
ORDER_ID INTEGER PRIMARY KEY,
CUS_ID NUMERIC NOT NULL,
EMP_ID NUMERIC NOT NULL REFERENCES EMPLOYEE (EMP_ID),
ORDER_AMOUNT NUMERIC(9,2) DEFAULT 0.00,
ORDER_DATE DATETIME NOT NULL,
ORDER_STATUS VARCHAR(50));


CREATE TABLE ORDERItem (
BAKEID INTEGER NOT NULL REFERENCES PASTRY (BAKEID),
ORDER_ID INTEGER NOT NULL REFERENCES ORDERs (ORDER_ID));

INSERT INTO PASTRY VALUES(101,'Apple Pie', 17.25 , 20);
INSERT INTO PASTRY VALUES(102,'Chocolate Roll', 15 , 10);
INSERT INTO PASTRY VALUES(103,'Eclair', 7 , 35);
INSERT INTO PASTRY VALUES(104,'Banana Muffin', 4 , 40);
INSERT INTO PASTRY VALUES(105,'Cinnamon Roll', 4 , 22);
INSERT INTO PASTRY VALUES(106,'Opera Cake', 30 , 6);
INSERT INTO PASTRY VALUES(107,'Macaron', 3 , 45);
INSERT INTO PASTRY VALUES(108,'Butter Roll', 12 , 12);
INSERT INTO PASTRY VALUES(109,'Coconut Cake', 15 , 46);
INSERT INTO PASTRY VALUES(110,'Rasberry Cake', 15 , 10);
INSERT INTO PASTRY VALUES(111,'Strawberry Moussee', 19 , 10);

INSERT INTO ORDERs VALUES(111, 10011, 111, 92.50,'2017-11-03', 'done');
INSERT INTO ORDERs VALUES(112, 10012, 112, 98,'2017-11-03', 'waiting');
INSERT INTO ORDERs VALUES(113, 10013, 113, 100, '2017-11-03', 'waiting');
INSERT INTO ORDERs VALUES(114, 10014, 114, 158.58,'2017-11-03', 'partially done');
INSERT INTO ORDERs VALUES(115, 10015, 115, 25,'2017-11-03', 'done');
INSERT INTO ORDERs VALUES(116, 10016, 116, 69,'2017-11-03', 'waiting');
INSERT INTO ORDERs VALUES(117, 10017, 117, 78,'2017-11-03', 'done');
INSERT INTO ORDERs VALUES(118, 10018, 118, 150,'2017-11-03', 'delivered');
INSERT INTO ORDERs VALUES(119, 10019, 119, 30,'2017-11-03', 'delivered');


INSERT INTO ORDERItem VALUES(101, 10011);
INSERT INTO ORDERItem VALUES(102, 10012);
INSERT INTO ORDERItem VALUES(103, 10013);
INSERT INTO ORDERItem VALUES(104, 10014);
INSERT INTO ORDERItem VALUES(105, 10015);
INSERT INTO ORDERItem VALUES(106, 10016);
INSERT INTO ORDERItem VALUES(107, 10017);
INSERT INTO ORDERItem VALUES(108, 10018);
INSERT INTO ORDERItem VALUES(109, 10019);


INSERT INTO CUSTOMER VALUES(10011,'Will', 'Smith','985-859-9856' , 25.60);
INSERT INTO CUSTOMER VALUES(10012,'John', 'Destiny','562-985-3654' , 85);
INSERT INTO CUSTOMER VALUES(10013,'Derek', 'Alison','817-125-3685', 283.36);
INSERT INTO CUSTOMER VALUES(10014,'Shawn', 'Brown','817-259-3506' , 85.20);
INSERT INTO CUSTOMER VALUES(10015,'Peter', 'Hobbs','817-254-0301' , 45.52);
INSERT INTO CUSTOMER VALUES(10016,'Bob', 'Shawn','817-825-0258' , 85);
INSERT INTO CUSTOMER VALUES(10017,'Leif', 'Sean','857-003-6985' , 25);
INSERT INTO CUSTOMER VALUES(10018,'Holland', 'Leif','859-559-6975' , 20);
INSERT INTO CUSTOMER VALUES(10019,'Myers', 'Sergio','125-254-6548' , 45.30);


INSERT INTO EMPLOYEE VALUES(111, 'Eliza', 'Johnson','817-458-0252');
INSERT INTO EMPLOYEE VALUES(112, 'Ella', 'Zhao','817-458-0325');
INSERT INTO EMPLOYEE VALUES(113, 'Lisa', 'Swift','817-125-2587');
INSERT INTO EMPLOYEE VALUES(114, 'Noah', 'Hope','817-025-3584');
INSERT INTO EMPLOYEE VALUES(115, 'Alex', 'Alison','258-256-0369');
INSERT INTO EMPLOYEE VALUES(116, 'Emma', 'Morgan', '256-025-4856');
INSERT INTO EMPLOYEE VALUES(117, 'Taylor', 'Doe','817-365-6975');
INSERT INTO EMPLOYEE VALUES(118, 'Taylor', 'Dannon','817-458-6812');
INSERT INTO EMPLOYEE VALUES(119, 'Taylor', 'Williams','817-005-6855');

COMMIT;
Posted
Updated 11-Apr-20 17:18pm
Comments
Afzaal Ahmad Zeeshan 11-Apr-20 22:48pm
   
I believe you are hardcoding the content, that is weird. You should come up with a dashboard or front-end that will manage the layer for data creation and query.
Member 14007867 12-Apr-20 21:30pm
   
Thank you so much! I'm working on it!

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

This is similar to a basic eCommerce schema; and there are a few things you may want to consider in your setup.

The first would be in your Order table. Often times places will have discounts or coupons, so you may want to have columns for those and possible one for taxes as well.

The second would be in your OrderItem table. It should definitely have a column added for Quantity. I would also add in a column for price. I know the reference to the Pastry table would give us the price; however, if their is a price change you lose your data integrity.
   
Comments
Member 14007867 12-Apr-20 21:29pm
   
Thank you so much for your recommendations.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100