Best way of structuring inventory tables with multiple 'warehouses'?

[libertyhill]

I’m creating an inventory tracking app for a boutique hotel chain with 10 separate little 2-4 room buildings. The inventory in each building needs to be tracked separately, as if each building has its own ‘warehouse’ of inventory.

If there were just one property this would be simple, but since there are 10 separate properties I’m a bit stuck on the best way to do this in AppSheet. Would you create a separate inventory table for each property? Or would you create one inventory table, with multiple rows for the same item type (one row per property). Or would you structure it a different way that I haven’t thought of?

[MultiTech]

libertyhill:

Or would you create one inventory table, with multiple rows for the same item type (one row per property)

More along that route is what I would do.

I would create the following:

• Properties
• Inventory
• Property Inventory

This way you can create the Inventory records as a sort of “Master List” - then create the Property Inventory to keep track of what’s actually there.

If you create a ref connection between the tables, you’ll end up with a very nice list of related records for each property and inventory item.

help.appsheet.com

References Between Tables

Learn about references, when to create them, and how to use them so that your data makes sense.

[libertyhill]

Thanks @MultiTech_Visions – I’ve attached an example picture below to ensure I understand what you are proposing. Is this what you were thinking? I have a few questions about this.

1. If I have to duplicate each item in the Building Inventory list then what is the purpose/benefit of having the Inventory table or the Buildings table? Genuinely curious if they would be used for anything.

2. Using this method, if I have to use pictures etc for each item, will I need to copy / paste the picture link to the item each time for a new property?

[MultiTech]

libertyhill:

I’ve attached an example picture below

Seems that got cut off…

---

libertyhill:

If I have to duplicate each item in the Building Inventory list then what is the purpose/benefit of having the Inventory table or the Buildings table? Genuinely curious if they would be used for anything.

With traditional database design schemas, you’ll have these “support” type of tables, where they provide you with a master list of options. There’s multiple benefits from this type of data schema, just a few of the main ones include:

1. Easy maintenance of the lists (if you have a table listing all your properties, you can say to any dropdown where you select a property - go get the list of properties; if you manually enter the options (like in an enum) then you have to go into the app and change that list. If you have multiple places that call on that list… then you’ll have to go change them all.)
2. AppSheet is built for this archetecture, so you’ll get nice “sub-views” (which we call “Inline views”) like a small table showing you all the related property inventory at that location. The real benefit here is that you can format things nicely and create an slick interface.
3. The parent table (which is what you would call the Inventory and Buildings tables) can store information their subjects in fields - such as the inventory table having the name, an image, the cost, etc. By storing this information in a parent table, you can easily pull this into the child table (which is what you would call the Building Inventory table).

Nice segue to…

libertyhill:

Using this method, if I have to use pictures etc for each item, will I need to copy / paste the picture link to the item each time for a new property?

As you can see, with the parent-child reference connection you’ll be able to input info into one record (like a photo) then just pull that info using a dereference when you need it again. The nice thing here is that even if you “Copy the image into a new record” the app doesn’t actually save a bunch of duplicates, it knows you’re using the same image and just pulls the one.

help.appsheet.com

Dereference Expressions

Use a dereference expression to retrieve a field value from a referenced record.

[libertyhill]

Awesome, thanks for the advice.

[MultiTech]

You’re welcome! (^_^)