The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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

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.

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

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.