I am currently working on building a web app to track products in an inventory. I am very new to database design and using Ecto library. My app idea is for tracking automotive spare parts. There are different types of parts that I want to track, e.g, “Air Filter”, “Shock Suspension”, etc. For each product, I am tracking oem_number, brand and qty. However, there are additional fields that I want to tracking depending on what product type it is. Eg. for “Air Filter” I am tracking additional field called “sakura_number” and for “Shock Suspension” , I am tracking additional fields called “direction” and “side”. I know which product type has what additional field/ fields, so I will already create product types table and its associated attributes ahead. The following is what I have so far.
The challenge that I am facing right now is how to add a product to the product table through web UI form.
I want the form input fields to reflect what data I need to enter when adding a new product. Here is my very dumb thought process. First, I will select the product type, load associated attribute names for that. Now, in my form, I will have the common fields like oem_number, etc. Then, I got so lost now.
I would really appreciate any thoughts/ opinions/ ideas or resources that I can go and learn more above solving similar issue that I am facing.
Why dumb? It’s hard for me to imagine doing it any other way. Once you loaded the attributes related to a selected type, you will need to use this to dynamically build a form. How exactly do that and later how to save this whole graph of records depends heavily on what technologies you use on frontend. Is it LiveView? “Regular” views? Some frontend framework like React and using Elixir (and, presumably, Phoenix) just as an API?
One comment to your database design though - I would replace attribute_name in product_attributes with attribute_id referencing the id from type_attributes.
Thank you so much for taking the time to look at my database design and offer feedback.
You also helped me think about asking the right question. Yes, so I have attempted the dynamically building a form with LiveView and things got really messy here. I started questioning may be LiveView is not the right technology.
So, here’s my attempt. Since I know which product type has what additional attributes, I have seeded the database with all these information. Here’s my user journey for adding a new product via a form. First, I will be able to see all the product types and then once I click on of them, I will be redirected to a product listing page that only shows the products that are associated the product type I clicked on. That means, I can pass the product_type’s id via “params” and used that to get the products. This is where I want to have a button to create a new product.
Now to build the dynamic form, here is my thought. I know that I need to work on two things, context functions to get proper informations and use that to build the form. I can pass the pruduct_type’s id via params to get its associated type attributes. Now how to I used that information to create inputs? Inside a for loop? And how should I target which input field is for what table? Nested forms? This is where I got lost. I am only familiar with forms for inserting into one table but in my case I am inserting into product table and product_attributes table.
Again, I truly appreciate your guidance. People like you make the learning experience much better.
A tradition design of this type would commonly separate the product (or “material”) structure from the inventory. Something like:
product ← inventory in location → location
Since you may at some time have inventory in more than one location.
Secondly, you might find you have multiple nested product types. For example “Suspension” might have categories “springs”, “shock absorbers” and under “shock absorbers” you might have “coil-overs” and “mono tube”. And sometimes a product can be in multiple categories. So you may consider a category/type hierarchy - not just a single type.
One more thing: the product you buy might be a wholesale SKU and the product you sell might be a retail SKU (I don’t know your requirements of course). In which case you may need to further model a BOM to cater for those packaging differences.
Product Information Management is a very interesting topic (for me) so apologies if I’ve gone off on a tangent to your needs.
I’ve used this pattern before, as product ← stock → location; then storing the quantity on that stock join table. It works well and makes it easy to do aggregates to take full stock across all locations or subsets. Transactions allow you to “move” products across locations safely by bundling together an increment in one location with a decrement in another.
You’re on the right track, this sort of meta-attribute pattern is very common for fluid structuring of data. @katafrakt is correct in observing that you need your attribute_values table to join back to your attribute_definitions table, and that the UX around constructing these records is essential and challenging.
Yup, nested forms. Once you get the underlying data model right, you’ll find that the “how” of building these forms becomes more straightforward than you’d think; it’s getting that data model right that is hard. Start with “for every new product, I should be able to create N empty changesets representing new attribute-value records based on attribute-definitions, and render them as nested forms, such that when submitted, all relationships are built out”. Where that feels hard, you are probably missing something important in your data model that needs to be captured as a (potentially hidden) field in your nested forms, iterate until it becomes easy. Then move on to the editing-existing-values experience.
Additionally, in a real-world application you’re going to need to think about the typing of these attributes, as well—unless every attribute value will always be a string rather than, say, a date or integer. Since you’re departing from strongly-structured SQL you’ll also want to think through null-ability and required-ness semantics and implement them yourself.
