Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    49

    Unanswered: need help on design tables

    i have designed tables for my store database. the purpose of my database is to make invoice for customer and input my buy invoice, calculate stock of my goods in the warehouse. here is the complete list :
    PRODUCT TABLE
    FIELD :
    * PRODUCT ID
    PRODUCT NAME
    FIRST STOCK

    CUSTOMER TABLE
    FIELD :
    * CUSTOMER ID
    CUSTOMER NAME
    etc....

    SUPPLIER TABLE
    FIELD :
    *SUPPLIER ID
    SUPPLIER NAME
    etc.....

    TRANSACTION TABLE
    FIELD :
    * INVOICE ID (AUTONUMBER)
    SUPPLIER ID
    BUY INVOICE NUMBER ( INVOICE NUMBER FROM SUPPLIER, CREATED BY SUPPLIER)
    BUY INVOICE DATE
    SELL INVOICE NUMBER (INVOICE NUMBER FROM MY STORE)
    CUSTOMER ID
    SELL INVOICE DATE

    TRANSACTION DETAIL TABLE
    FIELD :
    * TRANSACTION ID (AUTONUMBER)
    PRODUCT ID
    PRODUCT NAME
    QUANTITY IN (BUY)
    BUY PRICE
    QUANTITY OUT (SELL)
    SELL PRICE

    THE RELATIONSHIPS ARE :
    PRODUCT ID [PRODUCT TABLE] WITH PRODUCT ID [TRANSACTION DETAIL TABLE] (1 TO MANY)
    INVOICE ID [TRANSACTION TABLE] WITH INVOICE ID [TRANSACTION DETAIL TABLE] (1 TO MANY)
    SUPPLIER ID [SUPPLIER TABLE] WITH SUPPLIER ID [TRANSACTION TABLE] (1 TO MANY)
    CUSTOMER ID [CUSTOMER TABLE] WITH CUSTOMER ID [TRANSACTION TABLE] (1 TO MANY)


    is my design right? anyone help me to design the right way please.
    the problem in my design is i can't check the stock of my goods before any transaction (buy or sell) of the goods. But after any transaction occur i can check it and it add the first stock of the good in my inventory. i want to know if it is possible to me to add the first stock of my goods and i can check it before any transaction occur.
    thank you, sorry my english is bad.
    __________________
    hendra gunadi
    hendra gunadi

  2. #2
    Join Date
    Dec 2003
    Posts
    18

    Re: need help on design tables

    Hi there,

    If you have covered all the information you think you want to be able to manipulate, your current table structure seems good. Besides the table names and fields names, have you thought about each field's properties.
    For instance, how many characters long, format, input mask (if needed), etcetera.

    I have had the same concerns as you in my early projects. I consulted an expert whose response turns out to be very true. At the first you do the best you can with the design and purpose you have in mind now. Think it out and put it on paper. Now you are going to start to design forms and reports to access and manipulate your table information and as you do that your original design concept may change a bit, even to the point of altering a table design or table relationships slightly. This is all normal and to be expected. It is typical of the creative process.

    Hope this helps.

    Amadea

Posting Permissions

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