Need help for my database design

[jemz]

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.

[r937]

any chance you can post a text file, please

[jemz]

any chance you can post a text file, please

Hi, Thank you for the reply.here it is.

[r937]

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

[r937]

what's a "cakedr"? a cake doctor?

you said you had doubts... please elaborate on what doubts you have

[jemz]

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.

[r937]

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

[jemz]

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...

[squeal]

Stand back. I'm a fully-qualified cake doctor.

[K. Wolfe]

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...

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.

[jemz]

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.