Help for DB design

Hello,

I want to create an application to manage the audiovisual equipement (micro, camera, etc) for my company (audioviual production).

We have various pieces of equipment, the inventory has been carried out and each piece of equipment is now referenced in the same table (in bubble).

I now need to generate material reservation sheets, directly linked to the material inventory.

What do you think is the best way to organize the database?

Store in a new table a list of equipment that is reserved? With a single case to store all the IDs of the articles concerned? Or with a large number of columns to store one by one the IDs of the equipment concerned?

I am interesting about all advice,

Thanks,
Clement

FWIW:

I would have:

Inventory: this is your table to store all of your inventory. I’d have a status field in there for “Available”, ”Retired” and “Not available”.

Staff: this table contains the names of all the people who can check out your inventory.

InventoryHistory: this is your table that would have a single row added every time someone checks out a piece of equipment.

  • userID
  • inventoryID
  • dateCheckedOut
  • dateDueBack

When someone checks out something, update the corresponding Inventory row’s status to “Not available”.

When they return it, your app can set the status back to “Available”.

If a piece of equipment is broken, you can set the status to “Retired”.

When people want to check something out, you can show them all items that are not retired. For each item you can show them if it’s available or if it’s been checked out (along with the date it’s going to be returned and made available again).

If you want to get fancy, you can add an option to let the user reserve the item when it gets returned. It would just be another InventoryHistory row with future dates. When the first user returns the item you can have the system find the first reserved row for the item and set the item back to “Not available”, and maybe even shoot them a message saying the item’s available.

I’m sure I missed a lot of details, but this might be a good start.

Hi @firstfifteensoftware,

Thank’s a lot for your answer and the time you take to write this with so many details !
As I read your message, I realize my question was not very clear.

I don’t need to do this inventory again, I did once to list the equipment we’ve got, but now that’s done, I have my list, and it’s definitive, except if equipment is broken/stolen, or we buy new things. In this case, we can update manually.

In an attempt to be clearer, here is another way of explanation :

Let’s say we have a total of 4 cameras (a,b,c and d) , 3 mics (a,b and c) and 3 spotlights (a,b and c). So we have a table called inventory with 10 rows (with all the equipment we have, in reality : 250 equipments).
I also have a table called project for project details (name, date etc.).

In two weeks, on Monday, I have an event call “First event of the year” and I need to use 3 cameras (a, b and c), 1 mic (a) and 2 spotlights (a and b).

To write this in a table, I see this options :

  • a table with 1 field for project ID and 1 field for the list of equipment ID (list of values), so a field who can contain 50 or more ID.
  • a table with 1 field for project ID and many fields (equipment 1, equipment 2, […], equipment 250), all with only one equipment ID
  • a table with 1 field for project name and one field for equipment ID, only one. This table will contain much more row, but each row will be simpler.

Is there a better way to choose ?

Thanks,
Clément

OK, thanks for the clarification!

I think both Option 1 and Option 2 would be valid ways to approach this.

I know that there could be performance issues with too many items in a single list element, but it sounds like you may be fine, given your situation.

Personally, I would go with Option 3, but I’m kind of “old school” when it comes to db design. Option 1 would probably be considered more “Bubbley”.

thank’s for your feedback, I also like the 3th option but the 1st is much easier to set up and seems to be less CPU consumption. But I need to learn more about list, how to find element, ect.

My one-year Bubble anniversary is coming up in just a few days, and lists are still a bit of a mystery to me. But mastering lists seems to be pretty important in Bubble.

Good luck!