Inventory: https://599cd.com/Inventory

In order to help, we will need to have more detail on how your system works.  This is the first step to designing any database.  How are they being tracked now?  What's the process?  The more detail you can provide the better help you can get.

-Raymond

Manage Rental Inventory

Raymond Hi Raymond, I am building a basic database watching Rick's class: inventory management System and the subsequent one for tracking the stock. Instead of customers table, I created a Product table (linens products etc) and a Vendor Table (for orders).  I need to track how many in storage, how many in beds, how many out at laundry service and how many in the shelves available for daily use.  Do I need to create additional tables? and how do I handle calculations in the queries? Thanks

Stefanie Could you provide screenshots of what your tables/queries look like?

Yeah, this really depends on how detailed you want the system to be.

If you just need a simple solution, you could store separate quantity fields in your product table, like QtyInStorage, QtyInRooms, QtyAtLaundry, and QtyOnShelf. That works fine for a small setup if all you care about is the current totals.

But if you want a better long-term design, I would usually recommend tracking inventory movements in a separate table. In other words, keep your product table for the linen items, then have another table that records when items move from storage to rooms, from rooms to laundry, back from laundry to shelves, and so on. That gives you a history of where everything went and lets you calculate your totals more reliably. You'd track quantity moved along with dates, the employee that did it, etc.

So yes, you may need additional tables depending on how far you want to take it. For a boutique hotel with only 50 rooms, the simple method may be enough to get started. But if you think this database will grow, I would lean toward using a transaction table instead of just adding more quantity fields.

If you want, post a quick sample of your tables and I (and the other guys) can point you in the right direction. I do have a Barcode Seminar that talks more about tracking stuff like this - inventory ins and outs. I know you can't put barcodes on your linen (iron-ons? LOL), but the concepts are the same.

I do plan on revisiting this more in future Developer lessons too.

Yes, I think the simple method would work but I will try building one tracking movements if I am able to.  Thank you to you all for the prompt responses.