Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2002
    Location
    montreal
    Posts
    3

    Question design problems, HELP!

    hi all,

    I am currently designing a database for a small company scenario. The case will sound a little bit simplistic but i find myself turning in circles on some points.

    This little company imports items(like a watch, or a bracelet) :
    Problem no 1: how to modelise the Item table (and its associated tables)?

    ex: a Timex watch can be of size (,8,9,10) and of color (yellow,red green...)

    how do i modelize my items so that on the invoice i know what exactly is being sold? how much i have in stock, on hand ?
    will the type Timex have 1 unique item # number? or will every combination have an item number?

    Now a customer makes an ORDER, the order contains ORDERED ITEMS, i assume the orderDate falls under ORDER table, and quantity falls under ordered items. This will create an invoice, the invoice will contain the invoice#, order#, date, and amount. In order to keep track of the company's sales, should the invoice update a CustomerAccount Table?
    if the customer decides to return some of the items, should i create a CREDITMEMO table? or simply update a field under ORDEREDITEMS like quantityReturned?

    What if when the customer makes a payment, is custID and payment date enuff to identify the customer's payment? if no, what info is necessary? then i need aCUSTOMERACCOUNT table to update a customer's balance? if a customer pays a check or by credit, how to deal with the check no? or the credit expiration date? ccnumber?

    I would really appreciate any guidance!!!!!
    desperately yours,
    Adonis

  2. #2
    Join Date
    Dec 2001
    Location
    Delhi/Bangalore, India
    Posts
    74
    I'm in a hurry, and here's a short hint for the items table. Try to visualise it and tell me:

    ITEMS table:

    Have a TYPE sort of field, which will contain things like "WATCH", "MOBILEPHONE".

    Now in the same table, create fields "INFO1", "INFO2", "INFO3",..."INFON".

    DETAILS table:

    This will map things like "WATCH" to "DETAIL1", "DETAIL2",.. (DETAIL1 is textual description of INFO1. e.g. "SIZE")

    So now, to store details for a TIMEX watch, the ITEMS table would contain:


    SNO. ----- TYPE ------ COMPANY ----- INFO1 ---- INFO2
    54. ------- WATCH ----- TIMEX -------- 8 ----- YELLOW

    The DETAILS table would contain:

    SNO. ----TYPE ----DETAIL1------DETAIL2
    3. -----WATCH----- Size--------- Colour


    What do you think?

  3. #3
    Join Date
    Mar 2002
    Location
    montreal
    Posts
    3

    problem details

    Manish, thank you very much for your time, I will provide a detailed description of the problem.

    the item can be a bracelet (category bracelet)

    a bracelet has the following caracteristics :
    item descr: Bark Modele Xtra Long Regulier
    type : Flat Padded
    Color & size (color and size do not affect the price)
    Exemple :
    item# description Modele Type color Size Price
    111 Bark XtraLong Flat red 9 30$2345 Bark Regular Padded Rouge 9 25$
    2312 Bark XtraLong Padded vert 7 10$
    122 Bark Regular Flat vert 7 11$

    also add Qty in stock for each type...
    I can link time# and cat# easily together, but how do i link item# to the reste of its caracteristics? ie to color, size, quantity!!!

    thanks in advance for your time!
    adonis

  4. #4
    Join Date
    Mar 2002
    Location
    montreal
    Posts
    3

    problem details

    Manish, thank you very much for your time, I will provide a detailed description of the problem.

    the item can be a bracelet (category bracelet)

    a bracelet has the following caracteristics :
    item descr: Bark Modele Xtra Long Regulier
    type : Flat Padded
    Color & size (color and size do not affect the price)
    Exemple :
    item# description Modele Type color Size Price
    111 Bark XtraLong Flat red 9 30$2345 Bark Regular Padded Rouge 9 25$
    2312 Bark XtraLong Padded vert 7 10$
    122 Bark Regular Flat vert 7 11$

    also add Qty in stock for each type...
    I can link time# and cat# easily together, but how do i link item# to the reste of its caracteristics? ie to color, size, quantity!!!

    thanks in advance for your time!
    adonis

Posting Permissions

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