Hi, can i ask some help for my db design,i want to make cake ordering…but i have some doubt on my tables and my fields,i am not good in this db design,…I hope you can help me.
Please see my attachment.
Thank you in advance.
Hi, can i ask some help for my db design,i want to make cake ordering…but i have some doubt on my tables and my fields,i am not good in this db design,…I hope you can help me.
Please see my attachment.
Thank you in advance.
any chance you can post a text file, please
Hi, Thank you for the reply.here it is.
even better –
TABLES (CAKE,CAKEDR,CAKEDRDETAIL,CAKEORDER,CAKEORDERDETAIL,CUSTOMER)
CAKE:
FIELD NAME: DATA TYPE:
CAKE_NO AUTONUMBER
CAKE_OCCASSIONNAME TEXT
CAKE_PRICE NUMBER
CAKEDR:
FIELD NAME: DATA TYPE:
CAKE_DRNO AUTONUMBER
CAKE_DRDATE DATE/TIME
CAKE_DRCUSNO TEXT
CAKE_DRSHIPDATE DATE/TIME
CAKE_DRDELIVEREDBY TEXT
CAKE_DRRECIEVEBY TEXT
CAKE_DRRECSTATUS TEXT
CAKEDRDETAIL:
FIELD NAME: DATA TYPE:
CAKE_DRDNO AUTONUMBER
CAKE_DRDQTYDEL NUMBER
CAKE_DRDPRICE NUMBER
CAKE_DRDAMOUNT NUMBER
CAKE_DRDRECSTATUS TEXT
CAKEORDER:
FIELD NAME: DATA TYPE:
cakeorderno AUTONUMBER
customerno TEXT
customer_orderdate Date/Time
customer_total NUMBER
customer_status customer_status
CAKEORDERDETAIL:
FIELD NAME: DATA TYPE:
cake_ord_no AUTONUMBER
cake_ord_custno TEXT
cake_ord_occasionname TEXT
cake_ord_qty NUMBER
cake_ord_price NUMBER
cake_ord_amt NUMBER
cak_ord_status TEXT
CUSTOMER:
FIELD NAME: DATA TYPE:
CUSTNO TEXT
FIRSTNAME TEXT
MIDDLENAME TEXT
LASTNAME TEXT
CITY TEXT
ZIPCODE TEXT
STATUS TEXT
what’s a “cakedr”? a cake doctor?
you said you had doubts… please elaborate on what doubts you have
Hi, cakedr is cake delivery.
that is created in ms access i transferred to the wamp,so this is the table that i created.
TABLES (CAKE,CAKEDR,CAKEDRDETAIL,CAKEORDER,CAKEORDERDETAIL,CUSTOMER)
CAKE:
FIELD NAME: DATA TYPE:
CAKE_NO INT AUTO_INCREMENT PRIMARY KEY
CAKE_OCCASSIONNAME VARCHAR(50)
CAKE_PRICE DECIMAL(3,2)
CAKEDR:
FIELD NAME: DATA TYPE:
CAKE_DRNO INT (5) AUTO_INCREMENT PRIMARY KEY
CAKE_DRDATE DATE
CAKE_DRCUSNO VARCHAR(5)
CAKE_DRSHIPDATE DATE
CAKE_DRDELIVEREDBY VARCHAR(50)
CAKE_DRRECIEVEBY VARCHAR(50)
CAKE_DRRECSTATUS CHAR(1)
CAKEDRDETAIL:
FIELD NAME: DATA TYPE:
CAKE_DRDNO INT (5) AUTO_INCREMENT PRIMARY KEY
CAKE_DRD_DRNO INT(5)
CAKE_DRDQTYDEL INT
CAKE_DRDPRICE DECIMAL(3,2)
CAKE_DRDAMOUNT DECIMAL(8,2)
CAKE_DRDRECSTATUS CHAR(1)
CAKEORDER:
FIELD NAME: DATA TYPE:
CAKE_ORDERNO INT (5) AUTO_INCREMENT PRIMARY KEY
CAKE_ORDCUSNO VARCHAR(5)
CAKE_ORDERDATE Date
CAKE_ORDERTOTAL DECIMAL(10,2)
CAKE_ORDERSTATUS CHAR(1)
CAKEORDERDETAIL:
FIELD NAME: DATA TYPE:
cake_ord_no INT (5) AUTO_INCREMENT PRIMARY KEY
CAKE_ORD_ORDERNO INT (5)
cake_ord_custno VARCHAR(5)
cake_ord_occasionname VARHCAR(50)
cake_ord_qty INT
cake_ord_price INT
cake_ord_amt DECIMAL(8,2)
cak_ord_status CHAR(1)
CUSTOMER:
FIELD NAME: DATA TYPE:
INDEXID INT AUTO_INCREMENT INDEX
CUSTNO VARCHAR(5) PRIMARY KEY
FIRSTNAME VARHCAR(50)
MIDDLENAME VARHCAR(50)
LASTNAME VARHCAR(50)
CITY VARHCAR(50)
ZIPCODE VARHCAR(15)
STATUS CHAR(1)
I have doubt in my column fields datatypes and using the index,i am confuse in this index and primary key…and also in the relationship.I hope you can help me.Thank you in advance.
datatypes are in general okay, except DECIMAL(3,2) seems too small
customer table doesn’t need an auto_increment if you make custno VARCHAR(5) the primary key
what i suggest for your next step is to populate these tables with sample data… that usually helps uncover additional issues
Hi,about this
DECIMAL(3,2) seems too small
is this okay Decimal(5,2)
about my custno what should i do for that?do i need to change to int(5) and then auto-increment?
I want my custno when i am going to Add new customer.the custno should be like this
custno: 10000
and then add another customer
custno: 20000…i don’t want to input the custno and i want to be generated or auto_increment…
Stand back. I’m a fully-qualified cake doctor.
After creating your table:
ALTER CUSTOMERS AUTO_INCREMENT = 1000;
This will start your autoincrement a little higher than 1 just so it looks ‘cooler’. But it will increment by 1, so your second user will be 1001.
Hi K. Wolfe, thank you for this.