Inventory System Product Model Design

[DtrollMC]

I am working on designing an Inventory Management Software, using JSP, JPA, and either SQLite or MySQL. The software needs to catalog a company's products, their names, desc, cost, price, etc. That could be represented by a Product object like this:

 

But in this IMS, I want to be able to track product expiry. For example, a company that sells scones buys Milk and Cream, some bags have different expiration dates than others. So I thought to have another item, ProductItem, exist as a Many to One relationship to the Product object, and would then have an expiry property as well as a quantity:

That way, the Product class can calculate the total stock of the specific item by adding up the quantities of all ProductItems with a matching product. 

 

I am looking for ways to improve this design. Is there a more efficient way to go about tracking different data on pieces of a whole?

Edited March 31, 2018 by DtrollMC
Many to One

[programmer]

If this is academic, how you track would depend on how you want to handle expiration. When things expire in "batches", say items on a shelf with UPCs based on expiration date or manually inventoried cases in a fast food freezer, just tracking the date and count makes more sense:

inventory:
productId, warehouseId/storeId, expirationDate, count

In a situation where each item could actually be tracked separately (like a vending machine), actually tracking each item would be more like:

invetory:
id, productId, warehouseId/storeId, expirationDate

If you are worried about query performance - it doesn't sound like the volume of data is large enough that it will be an issue. If you will be handling large volumes of data, you will want to learn how to write indexes or store procedures based on the queries you will be calling.

 

For a better answer, provide a bit more background on what your building will be used for (class, real product, personal business, etc.)

[DtrollMC]

7 hours ago, Erik Sieghart said:

I think you mean Many to One ProductItem to Product.

yes, as that is the association shown in the diagram.

[DtrollMC]

9 hours ago, programmer said:

If this is academic, how you track would depend on how you want to handle expiration. When things expire in "batches", say items on a shelf with UPCs based on expiration date or manually inventoried cases in a fast food freezer, just tracking the date and count makes more sense:

inventory:
productId, warehouseId/storeId, expirationDate, count

In a situation where each item could actually be tracked separately (like a vending machine), actually tracking each item would be more like:

invetory:
id, productId, warehouseId/storeId, expirationDate

If you are worried about query performance - it doesn't sound like the volume of data is large enough that it will be an issue. If you will be handling large volumes of data, you will want to learn how to write indexes or store procedures based on the queries you will be calling.

 

For a better answer, provide a bit more background on what your building will be used for (class, real product, personal business, etc.)

This is not academic, trying to learn more about J2EE, JPA, and business programming in general. Your first suggestion makes sense, have a Product table, and then an inventory table. 

 

When building this application I am imagining it for small businesses who still track most/all of their inventories / orders manually. I want the program to be scallable to larger businesses as well, could you expand on what you mean by learning how to write indexes or store procedures? If my store procedures you mean defining queries for the business objects, I will be doing that in the Entity classes.

[DtrollMC]

With the

@NamedQuery

annotation.

[programmer]

4 hours ago, DtrollMC said:

This is not academic, trying to learn more about J2EE, JPA, and business programming in general. Your first suggestion makes sense, have a Product table, and then an inventory table. 

 

When building this application I am imagining it for small businesses who still track most/all of their inventories / orders manually. I want the program to be scallable to larger businesses as well, could you expand on what you mean by learning how to write indexes or store procedures? If my store procedures you mean defining queries for the business objects, I will be doing that in the Entity classes.

I Am Not A Lawyer (and I don't play one on TV), but have you considered the liability of having a food safety feature in the software?If you are certain people would need that feature, consider talking to a lawyer to understand what if any exposure you would have.

 

A SQL index allows a query to read faster (and also typically make writing slower and use a bit more data) and a stored procedure is a query stored in the database which, due to being cached, will be faster than a normal query.

 

If you are going to try and make this for small business, I would avoid worrying about scaling to big business at first (they are very hard to sell to) and go read about how your target audience does their work.

 

Even better would be working with local businesses directly to understand their workflow and pain points...say through a part time job helping them with their IT needs getting some money and domain experience at the time.