The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Design for inventory database

I have developed an inventory database for restaurants, and have come across a problem with the design. I have a potential solution, but want to check it before I implement it.

The results in the database are calculated by restaurant, by StockPeriod (which is a week, but could be something different). So, you get results for the London branch for the week ending 01/01/2006.

This is the problem...

Say StockPeriod 1 is the first week of 2006 and StockPeriod 2 is the second week of 2006. Most of the restaurants finalised StockPeriod 1 on Sunday night --- except for 5 restaurants. It's now Wednesday and those 5 still haven't finalised StockPeriod 1. Since StockPeriod 1 ended, we have made some changes:
(1) The price of dough balls went up by 5%
(2) The Mushroom pizza recipe changed to include 4 extra mushrooms
(3) The box of tomatos delivered is now smaller, so we get less in a box.

These things all affect the calculation of the Cost Of Sales for a Mushroom pizza. Those 5 restaurants that are still in StockPeriod 1 are going to finalise soon and when they do, they'll be using the current data in the defintion tables, which is correct for StockPeriod 2 -- but they are in StockPeriod 1.

Our problem is that, although we save results data by StockPeriod, we only have the current version of out definition data.

The only solution that I can see is to identify definition tables that affect the results (eg. RecipeDetails) and store that data by StockPeriod. So the whole RecipeDetails tables is duplicated for each StockPeriod. That would make each of these tables 52x bigger each year (there are about 10 of these tables).

And, unfortunitely, I can't force the restaurants to finalise on time. Or hold back data updates until every restaurant has finalised.

This seems like a very messy solution to a problem that all inventory systems would have to deal with (I would think). Does anyone have any better ideas or suggestions. Or is my solution the only one there is ?

Any help would be appreciated.
Craig HB Send private email
Thursday, December 14, 2006
Could you create a seperate category for Overage, and move the info there? Then just close the StockPeriod automatically, working with the overage on a seperate basis.

Or, order a whole lot of mushroom pizzas!
Thursday, December 14, 2006
You could track definition detail changes over time. When calculations are being done in a certain Stock Period, calculate temp tables of definition details for that Stock Period. You could also keep temp Stock Period Definition tables around, but treat them as temp tables to be generated whenever they don't already exist, and purged whenever you clean out old stuff. They can always be regenerated based on source Definition Detail Tables that track changes over time.

Only problem here (although I don't think you mentioned it) is that Definition Details may be different per restaurant. One may be operating based on the older Mushroom pizza recipe in the same Stock Period that others have moved to the new one. But that may not be important for your reporting.
Ben Bryant
Thursday, December 14, 2006
Normally, you'd put an effective and expiry date on your definition data so you can find the data that was effective for a given period, without having to duplicate it for every period if it doesn't change.
Mike S Send private email
Thursday, December 14, 2006
Thanks for your responses.

Just to add something: the definition data is the same for all the restaurants. So when something changes (like a price or recipe), it changes for all the restaurants.

From the responses, it seems that a standard approach (and I like standard approaches) is to have an effective and expiry date. And then just use the definitions that match up with the correct dates. I guess in my case, I could make things simplier but putting an EffectiveStockPeriodID column in the definitions tables -- and use that to make sure I was using the correct definitions for that StockPeriod. And I could use temp tables to speed up the processing.

Thanks for you help and please keep them coming.
Craig HB Send private email
Thursday, December 14, 2006
Yes, start and expiry dates or stock periods is what I meant by tracking over time. Essentially for each kind of detail you'll have a record for every period that it has a different value, and then query details for a given stock period based on the start being less than or equal and the end being greater than or equal to the relevant stock period.
Ben Bryant
Thursday, December 14, 2006
For what you're trying to do, having one "definition table" makes sense. Err...  that is, if you're trying to accomplish the goal of balancing stock inventories each week.

Don't undersell the value of having every "definition table" created, all 52+ of them. Five years from now, that will be a gold mine to economists and forecasters; you're literally recording both how much stock was available that week as well as your company's response to that inventory.

There's an easy way to handle it. Let's say you have fifteen ingredients. Every week, you add those fifteen records to your table, and you indicate which week (and year) you added them. If you have no data for tomatoes that week, you can either carry over the previous week, or treat it as an error.

In the restaurant table (or the budget table), you simply perform your calculations on ingredient X as of week Y, like...

FROM StockPeriod
WHERE Ingredient = 'tomatoes' AND Period = '2006-01'

For your specific problem, that 5th restaurant that's late, just simply refers to the correct week rather than the current week. You would just have to make sure that going back and "updating" prior periods is forbidden, in order to keep the calculations correct (or otherwise publish revised results).
Thursday, December 14, 2006

You're basically still keeping ONE table.

And disk space/performance requirements are still trivial. On today's computers, the time to fetch data out of a table containing one stock period appears to be no different than one containing 520 stock periods (10 years).

You'd need to keep 100,000 years worth of records by my calculations before you have to start worry about performance issues.
Thursday, December 14, 2006
Thanks, TheDavid.

Looks like I should just have definitions for each StockPeriod. Especially since we are planning on moving the data into a data warehouse and build an OLAP cube on it. So having that historical data is valuable for us. That is what prompted this posting.

Also, space is not really an option -- it's just that I'm not used to archiving definition data like that.
Craig HB Send private email
Thursday, December 14, 2006
Whenever the manager/cook orders stock, store the quantity ordered and price paid in the database. This also handles the situation where product ordered twice a week might have 2 different prices.

When Mushroom Pizza Recipe v1 changes, it's now Mushroom Pizza Recipe v2. If you store the ingredient lists separately for each revision, then the database will be able to handle the case where the recipe changes in the middle of the week.

If the box of tomatoes delivered is now smaller, that's a different SKU.
Thursday, December 14, 2006
Add a version number field to the primary key of your parts records and bills of material.Also add an expiration date. Use nulls for the expiration date.

During processing create a temp table with a list of keys and version number where expiration date is null. Update the list with the minimum version number where the period date is less then or equal to the expiration date. Join on the list.
Phil C Send private email
Thursday, December 14, 2006
"When Mushroom Pizza Recipe v1 changes, it's now Mushroom Pizza Recipe v2. If you store the ingredient lists separately for each revision, then the database will be able to handle the case where the recipe changes in the middle of the week.

If the box of tomatoes delivered is now smaller, that's a different SKU."

I agree with the new SKU in principle, but my work with Bills of Material (i.e. recipes) shows that this causes its own problems. You'll also need a substutions table so that a request for one SKU can automatically cascade to the actual correct SKU. Without that substitutions table, you'll be forced to update the BoM. As you point out, that's actually a new BoM.

In itself, the new BoM might not seem like a problem, but now you need to have a way to relate the two BoMs so that you can track BoM changes over time (among other things!). That requires a solution that should sound familiar by now: a master record (Mushroom Pizza) with start-end dates.

In fact, a simple solution to change logs in general is to add a start-end to every table. The active entry has a blank end-date. To update a record, fill in the end-date, copy the data to a new record with a new start date and a blank end-date. To delete a record, fill in the end-date but don't copy to new. Personally, I hate working with these things, but I'm learning to cope.
Ron Porter Send private email
Friday, December 15, 2006
"If the box of tomatoes delivered is now smaller, that's a different SKU."

Well, it's a different SKU to your vendor, but your internal "tomatoes" StockNum doesn't have to change just because you're buying from a different vendor or buying in a different quantity.

Keep your own StockNums for each product used, and for each recipe indicate the units of that StockNum required.  Then it won't matter the quantities by which you order.

Calculate the cost of your inventory as of the date it was delivered, and any usage can be tied to that date.

The effective start/end date idea is a good one for recipes and other quantity holders that may change over time.
Karl Perry Send private email
Saturday, December 16, 2006
Re: "Start and End Dates"

My problem with this solution is that now all lookups across tables happen as "between" dates queries as opposed to some sort of a numeric matching.

Intuitively, it feels that this lookup (foriegn key match) will take much longer to do via dates than via a numeric equal-to. If this is the case it could be recipie for disaster in a high volume system.
Vaibhav Arya Send private email
Sunday, December 17, 2006
>> Intuitively, it feels that this lookup (foriegn key match) will take much longer to do via dates than via a numeric equal-to

Intuition is the enemy here, and the cause of many people deciding to do Bad Things like store their dates as character strings or as numerics. Store dates as dates.
David Aldridge Send private email
Monday, December 18, 2006
A different way to think about the question is... is StockPeriod truly the same across all restaurants?

From a theoretical perspective, yes it makes sense that all restaurants have the same stocking period, and even the policies might suggest as such.  But obviously in reality moving all restaurants on the same timeframe is a challenge.    A data model should address the reality, not just how things should work in theory.

So - you might think about tying the stock period to the individual restaurants.  You might have a default stock periods defined in one table, but have each restaurant store its own stock period.  This will allow you to keep the same calculation rules without introducing case conditions.
Yin-So Chen Send private email
Monday, December 18, 2006
"Intuitively, it feels that this lookup (foriegn key match) will take much longer to do via dates than via a numeric equal-to. If this is the case it could be recipie for disaster in a high volume system."

$100 says your date is stored as the number of (milli)seconds from epoch*.  All you'd wind up doing is the same thing it does internally, only externally.  Plus you'd loose all the cool date & timezone functionality already built into your database.

The only disaster in a high volume system would *not* be storing dates the way god intended.

Cory R. King
Tuesday, December 19, 2006
Er... my comment was useless,  sorry :-(

To make it somewhat useful, I'll add that if you went a date range route, I'd imagine your database has ways to tweak the indexes on the table.  While it is a bit above me, most have ways to sort the table physically based on an index--clustered tables, right?.  Plus if the query planner was smart, it would realize there are only one or two results for a given date range and adjust accordingly.
Cory R. King
Tuesday, December 19, 2006
I don't think your comment was useless at all.  "Your intuition is probably wrong" is a hard lesson a lot of programmers never learn properly.  Test and profile before deciding that working with Date or Timestamp columns is going to be slower than using an integer or number type of some sort.
Wednesday, December 20, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz