SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,107
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Post Need help for my database design

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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    any chance you can post a text file, please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,107
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    any chance you can post a text file, please

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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    even better --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    what's a "cakedr"? a cake doctor?

    you said you had doubts... please elaborate on what doubts you have
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,107
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.


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

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,107
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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...

  9. #9
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Stand back. I'm a fully-qualified cake doctor.

  10. #10
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by jemz View Post
    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:
    Code:
    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.

  11. #11
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,107
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    After creating your table:
    Code:
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •