Info needed for an inventory system

I'm building a point of sale for a meat market and need specific information as it relates to how inventory should be tracked. I want to design the database f开发者_如何学Cor this system but lack the knowledge needed. I already have a basic database that will handle products but need to implement something additional that will handle the weight of the meats.

There will be bulk inventory such as meat plus products that are sold. Is there anyone here that has some in-depth inventory control experience that can share ideas with me or maybe tell me where I can find this info without having to become an expert on the subject myself?

For example: The store owner purchases 100 lbs of beef brisket on Monday and enters that into his inventory and then on Tuesday purchases an additional 100 lbs from a different vendor at a different price. Both items have their respective ids in the database. If the store owner has for example 10 different vendors selling the exact same product, does that mean that I should store 10 different item ids in the database?

As their consultant, it is necessary for you to understand their data needs and be able to translate those needs into an appropriate schema that can fulfill those needs. I agree with what Oded and smirkingman have said, but to help you along in regards to your example:

you should not duplicate the item, but rather have separate tables to hold

• ITEM data

i.e. various meats

• VENDOR data

i.e. name, location

• TRANSACTION data

i.e. pounds of meat (identified w/ by foreign key to ITEM table)

vendor from which item were purchased (identified by foreign key to VENDOR table)

date of transaction, rate, total cost, etc.

This is just one way to do it, but our point is, you need to be well versed in not only their business needs, but also have the database knowledge to translate them into technical design/features.

And as always, clients/users NEVER know what they need, nor what they really want. It's also your job to not only ask them, but work with them to figure it out. This includes coming up with scenarios, primary use cases / stories, etc. - basically, what common/uncommon things do they need to track?

For example, in each transaction, do they need to maintain record of method of payment? What about the vendor representative(s) involved in the transaction? Will they need this information later for tax reporting? Budget / financial forecasting? What if the half the items are bad on arrival? Understand their process(es) and how they would handle each scenario and what they need to know, and then try to come up with technical solutions to address accordingly.

i manage a maintenance entity with a wherehouse. And we used to make inventory of the wharehouse in december. fore the same items bought from different vendors with different prices , the database compute the mean price of the items.

but as what is said above , it's important to know your client/users need. what is their procedure in accounting and you translate it in technical terms and design

A point of concern in inventory system, is basically looking into how to incorporate overheads, labour cost, value-added tax charges (if any) into your inventory, and how do you recognise their cost of sales when a delivery is made. Bitxwise has come out with some neat schema, and artoure put it, understand the accounting policy and tax ruling of your client before you start designing out the tables.