I am looking to start doing a sales database for a sweet shop my head seems to be spinning with ways to do this, as far as i can see i will have 4 tables user, stock, stockamt and trans. i need to be able to add new stock so i know how much is there, sell the stock with a running total for every item sold in the one transaction, i want to be able to put a barcode reader in here to. am i going in the right direction.
i am far from an expert on er modelling, but i am working on a similar project. in my case i added a few more tables. i have a purveyors table which is related to the product guide through a order_guide table. with this the owners can automate ordering. on the retail end, i would relate the trans table to the product guide through a trans_items table. this would eliminate your many to many relationship, and eliminate any unnecessary duplicates of data that you want to store with the transaction.
hope this helps
would it be posable to see what you have done to give me some good ideas. i named the that way because i cant spell. i know i am going to have truble if some 1 comes up with more than 1 item how to add them both, i was think of having a trans number for each sale and not for each item.
my application actually revolves around the back of the house (inventory, purchasing, etc.), and is not a point of sales system. As far as having issues with duplicates, that would be another advantage of having the order items table. this table would consist of a unique key, the order id, and the product id. one order could have several instances of a particular product, because the order items table would give each line item its unique key. as far as my application goes, i don't have a copy of my er model on the computer.