Storing dynamic fields in SQL Database

Question

I have a 'product' database for storing product details. For each product, it may optionally have 0 - 10 spare parts, with their names accepted through a dynamic form.

I'm unsure how the database-schema should be. From this thread, I got some ideas and this what I came up with.

 __________________________________
|products                          |
|*********                         |
|id, name, address                 |
|__________________________________|
 __________________________________
|spareparts                        |
|*********                         |
|id, name                          |
|__________________________________|
 __________________________________
|products_spareparts               |
|*********                         |
|id, product_id, sparepartid       |
|__________________________________|

So my idea is to have 10 rows in the 'spareparts' table since there will not be more than 10 spare parts for a product. Each row will have an ID, and the name field where the names accepted from the form will be stored.

When products are created, if there is a spare part, for each spare part it's name will be added to the 'spareparts' table, product_id and sparepart_id will hold the id of the product and the sparepart respectively.

The spare parts are created on a per-product basis. It's name is accepted from a form and two products may or may not have the same spare parts.

Would this work? Are there better ways to implement it?

If want you want is a many-to-many relationship between a list of Products and a list of Spare Parts then, yes, this is how you'd do that. Your description isn't entirely clear on that though. Are these two lists independent of one another? That is, would any given Spare Part belong to more than one Product? Or is a list of Spare Parts created on a per-Product basis and it doesn't really matter if any two Spare Parts for two different Products happen to be called the same thing? — David, May 14 '18 at 09:36
Reviewing my schema again, I can already see how this wouldn't work as each product can have entirely different names for each of their spareparts. — Inception, May 14 '18 at 09:36
@David: Spare parts are created on a per-product basis. It may or may not have the same name. The number of spare parts, and the name for each spare parts are accepted from the user through a form when creating a product. — Inception, May 14 '18 at 09:39
In that case it sounds like you just want a Products table and a Spare Parts table which has a foreign key back to that Products table. Such that any given Spare Part has exacly one parent Product, and any given Product has zero-to-many child Spare Parts. — David, May 14 '18 at 09:39
@David: So the spareparts table will simply hold a foreign key to the product associated and for each spare part, a new row gets added to the spare parts table? Then if there are two products with 5 spare parts each, the spare parts table will have 10 rows right? — Inception, May 14 '18 at 09:42
That's what it sounds like the description calls for, at least to me. — David, May 14 '18 at 09:43
Thanks. I guess I overthought how I should be implementing it while I was searching on how to handle values from dynamically created form elements. — Inception, May 14 '18 at 09:46
How they are entered shouldn't be a consideration for designing the tables. Ok, sometimes. But "reality" is. The real question is are some spare parts shared between products. If so, use the design in your question. Although how a new product is entered might be a consideration in a design, if widget A and widget B both use spare part 123, and some characteristic of the spare part changes (such as price), you do not want to have to change that in two places. — Sloan Thrasher, May 14 '18 at 09:46
@Inception, I believe your approach is correct. You should not have a foreign key in `spareparts` table to hold its respective product. Because if 5 products have 4 same spareparts, then you would only be duplicating the rows. Let `products_spareparts` table play its part, in indicating which product has what all spareparts. — Anadi Sharma, May 14 '18 at 09:47
@ SloanThrasher @AnadiSharma Thank you, but as I've described in the edit and in the above comments, since the spare parts are not defined already, I think I cannot use this approach. David's suggestion seems to be the correct one in this case. — Inception, May 14 '18 at 09:53

score 0 · Answer 1 · answered May 14 '18 at 10:21

 __________________________________
|products                          |
|*********                         |
|id, name, address                 |
|__________________________________|
 __________________________________
|spareparts                        |
|*********                         |
|id, name, productid               |
|__________________________________|

Referring to David's answer, I ended up using the above schema. productid is simply a foreign-key to the associated product. For each spare part, a new row gets added to the spareparts database with the respective product id. The third table is not needed in this case.

score 0 · Answer 2 · answered May 14 '18 at 11:02

Relational databases make it easy to limit relationships for 0/1 relationships. They do not make it easy for limiting the quantity for arbitrary numbers.

Basically, the reasonable solutions require using triggers. The trigger would count the current number of spare parts for a given product. If it exceeds some threshold (10 in your case), then you would get an error on an additional insert.

I would actually handle this by having a spare parts count in one of two ways. If I were just sticking to traditional database constructs, I would put a count of spare parts in products and use a check constraint on the value. A trigger would be used to keep the value up-to-date.

More likely, I would wrap all DML operations into stored procedures and do the check there. This requires that the DML operations are all handled through the stored procedures.

The "unreasonable" way is to have a list of ids as separate columns -- spareparts1, spareparts2, and so on. This allows you maintain the foreign key relationship and to limit the numbers. However, it makes subsequent queries on spare parts much more complicated.

The form itself handles the threshold problem as it does not allow more than 10 spare parts to be added for a product. So I do not think a trigger is required in this case. I do not really understand your 'unreasonable' way. Are you talking about adding those ids as separate columns in the spareparts database? — Inception, May 14 '18 at 11:08

Storing dynamic fields in SQL Database

2 Answers2