I have this interesting problem (to me) that I could use some help with.
The database is MySQL.
The background (simplified)
There's a catering company that has a list of available menus in a table called "menus". There is also a table called "inventory" which stores their entire inventory(cost, name, type, etc). In addition there is a table called "jobs" which stores particular job information such as which "menu" will be served. The last relevant table is "menuItems". "menuItems" stores each inventoryItem necc. to serve a particular "menu" and the perPerson ratio for each item. This includes not only the food involved but also the silverware and so forth. "menuItems" is populated by the item_id, the menu_id, and the perPerson ratio of items to people served.
Right now, there are about 1000 inventory items and 35 menus, so at least 35,000 "menuItems".
So, the user adds a new job and chooses a menu. Based on the ratios in "menuItems" I build an excel file(item,bring,return,used,cost,etc) for the user that acts like a packing list. It lists the items required and how much of each item to bring to the job and calculates the cost (based on today's cost of "inventoryItems") for the job.
Since jobs could have totaly different lists of items, I can just make a talble with all the inventory items, since they can add and remove items at will. I could store the items in an array but I am always weary of using arrays for data storage.
I could make a table like menuItems called packingItems or something that stores each item and how much they bring, return, cost, used etc, but this caterer does like 2000 jobs per year so I am affraid that the db will get to be way too big way too fast.
Can't figure out a better way. Point me somewhere!
Andy
dBforums
>
Design Problems
09-19-04, 09:34
Linear Mode
