SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    I want my 4th arrow! garlinto's Avatar
    Join Date
    Jun 2002
    Location
    Riding the electron wave
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    DB Design Question...

    I am almost done building a LAN-based Point-of-Sale system. I am somewhat new to mySQL, although I have worked with it for a few years now. I've only used mySQL for simple things like storing images and contact info for people that use my web-based contact forms. Now I want to do something a little more in-depth, and I need some suggestions.

    I want to be able to not only insert each invoice into the DB, but I also want to do so in a way that would allow me to provide various reports in a logical way. As it stands now, I have 1 table that I call "invoices", where I store all the info that is found on a completed invoice: customer's information, date, sale type, and sale details, which is a semi-colon delimited list of all the items, quantities, and prices for everything they bought.

    Now, after building that table and playing with it a little, it seems rather cumbersome to work with. I will have to do a lot of parsing in order to make reports like 10 Most Popular Items, Total Profit for the last week, Total Item Cost of all tickets for month of Nov, for example, since all of those details are in a list in one column.

    How would you guys build your table/tables to make such data storage and it's subsequent access more logical with less overhead? A seperate customers table linked with an items table? Or would you do something different?

    Any suggestions would be appreciated.
    Ducharme's Axiom: "If you view your problem closely
    enough, you will recognize yourself as part of the problem."


  2. #2
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    You are not following the rules of database normalization. By holding all of the items of a sale in the same record you are making the system cumbersome.

    Your best bet is to add two more tables, one of which holds all of the items and another that holds the item_id and the sales_id (this is called an associative entity). In the items table you would hold the item_id, item_name, price, etc. and in associative entity you could hold a quantity.

    Using that logic you would then be able to calculate how much was spent on certain products, how much a customer spent on a certain product over time and more. Having a customer table would also help your information make more sense.

    Take a look at this database design tutorial and see if that helps with some of the concepts.

  3. #3
    I want my 4th arrow! garlinto's Avatar
    Join Date
    Jun 2002
    Location
    Riding the electron wave
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hartmann, thank you for your insights.

    I currently have an items table that contains all of the product information for the 5000+ products we sell. I run queries against that table to populate the item details fields in the invoice form with the item descriptions and prices.

    I see what you are saying about the associative entity. That makes sense and I am kicking myself for mot seeing that solution earlier. Part of the reason I have been bottlenecked on this issue of DB design is that every three months or so, the prices change for some/most of the items contained in the items table.

    This creates a problem if, for example, I want to view an invoice from Joe Blow dated August 24, 2004. I might remember that he bought item 1 for 3.99, but in my current price table which I updated on December 30th, the item.price for that item is now 4.09. I would now have an inaccurate invoice and my accountant would kill me. So it seems like I would need to save the actual price paid for every item in the associative entity table?? Invoice_id, Item_id, Item_price? Otherwise, when I view reports/old invoices, the item prices may be all wrong, and so the invoices would NOT reflect the actual invoice created on that date.

    How would you change/adapt the DB design to reflect such fluctuations in data values over time? Does my suggestion work or are there factors I am not aware of?
    Ducharme's Axiom: "If you view your problem closely
    enough, you will recognize yourself as part of the problem."


  4. #4
    I want my 4th arrow! garlinto's Avatar
    Join Date
    Jun 2002
    Location
    Riding the electron wave
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow! There is soooo much to learn about relational databases! In doing some research, I have found that many of my DB designs have been very non-standard, making it very difficult and cumbersome to use them. Thanks for the reference Hartmann. That was very helpful.

    So I have reviewed my database design and have come up with a possible solution that maybe you can help me with.

    Proposed table design:
    Code:
        myDB.Customers:
          cust_id PK
          firstname
          lastname
          address
          city
          state
          zipcode
          telephone
          email
        
        myDB.Companies
          company_id PK
          company
          address
          city
          state
          zipcode
          telephone
          email
        
        myDB.invoices
          invoice_id
          cust_id
          company_id
          item_id
          item_price
        
        myDB.invioceDate
          invoice_id PK
          date
        
        myDB.items
          item_id
          ref_number
          part_number
          description
          price
          cost
    The reason I have included item_price in the invoices table is because prices change every three months, and I want to be able to retrieve the correct invoice from the database for any specific date.

    Any suggestions?
    Ducharme's Axiom: "If you view your problem closely
    enough, you will recognize yourself as part of the problem."


  5. #5
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great start

    I have made the assumption that an invoice can have more than 1 item on it. IMHO, you should make the following changes to your tables

    invoiceDate - Remove as it is not required

    invoices
    invoice_id
    cust_id
    company_id
    invoice_date

    invoice_items - new table
    ii_id (PK)
    invoice_id - link to invoices.invoice_id
    item_id - link to items.item_id
    item_price

    items - why is there a price and a cost field?

    Nowhere in the current design, do you have a field for the quantity of a particular item. Is this by design? i.e. can you only buy 1 of an item at a time rather than 2 or 3?

    You may want to consider extracting the address data from the companies and customers table and creating 1 address table.

    Hope this helps

  6. #6
    I want my 4th arrow! garlinto's Avatar
    Join Date
    Jun 2002
    Location
    Riding the electron wave
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excellent ideas! Thank you very much!

    Actually, the invoices can have multiple items with varying quantities. The fact that I didn't allow for item quantities was an oversight on my part. Thanks for the heads-up on that .

    So let me get this straight:
    Code:
         myDB.Customers:
           cust_id PK
           firstname
           lastname
           telephone
           email
         
         myDB.Companies
          company_id PK
           company
           telephone
           email
         
         myDB.Addresses
           cust_id
           company_id
           address
           city
           state
            zipcode
        
        myDB.invoices
            invoice_id
            cust_id
            company_id
            item_id
            item_price
          
          invoice_items - new table
            ii_id (PK)
            item_quantity (<-- would go here ??? )
            invoice_id - link to invoices.invoice_id
            item_id - link to items.item_id
            item_price
          
          myDB.items
           item_id
            ref_number
            part_number
            description
            price
        cost
    There is an items.price and an items.cost for a few reasons. 1) So that I find how much the item cost me, and 2) to retreive the retail price for the item.

    Now how does this look?
    Ducharme's Axiom: "If you view your problem closely
    enough, you will recognize yourself as part of the problem."



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
  •