First of all sorry for the long question in advance because I need to provide some base for my question.
I am working on a project for an international trading company with many buyers and suppliers.
The company is dealing with around 50 different products and more than 100 suppliers and more than 100 clients.
The main target of the app will be the following:
when the company receives an inquiry from one of its clients, they would like the app to filter the suppliers who can produce those materials.
The issue is about the endless options.
as an example I want to specify three products:
- Reinforcing Steel Bars
product_grade: can be 50 different grades
Specifications:
diameter (can be in mm or inch)
length (mm or meters)
- Wire Mesh:
product_grade: again various
Specifications:
thickness (mm or inch)
spacing: mm X mm
area: cm X cmm
- Painted Galvanized Coils
product_grades: many!!
Specifications:
thickness (mm or inch)
width (mm or inch)
length (mm or inch)
coating (g/mm2) (can also be front and back coating difference!!!)
color: various RAL codes
Now how should I structure my database that I can store all this information without redundancy. I can specify all details to every product while most of them will be stored empty.
Secondly, not all the producers produce the same sizes. Some mills produce wire mesh upto 20mm thickness but some can only do upto 10mm. Some produces various colors of Painted material some dont.
Also I need to find a nun redundant way to create inquiries that contains data that would be used to search the possible producers from the products and producers data bases.
My mind is about to blow up!!! Help me please!!
You may want to go the fully custom field route where fields are created dynamically.
Field
Name (text)
Category (Category)
Not sure if you are looking to do automatic calculations between fields, might need to rethink this one if so.
Supplier
Name (text)
Category this would be your Wire Mesh, Reinforcing Steel Bars
Name (text)
When creating a Category you would create some Fields associated with it as well. Later when you create a Product under a Category the fields are there to fill in values.
Product
Name (text)
Supplier (Supplier)
SKU (text)
Category (Category)
Price (number)
Image (image)
CombinedProperties (List of texts, this will a combination of all the fields=values used for searching later. Field kept up via via Database Trigger
e.g. : thickness=1 inch, width=2 inch, color=grey
Product Field
Product (Product)
Field (Field)
Value (text, let them type “1 inch” and come up with a way of standardizing the capitalization)
(changes of this field update the Product’s CombinedProperties)
–
So imagine a user wants to go look up some suppliers that have Wire Mesh that fits their needs, they search up Wire Mesh and pick it, then they are provided a list of fields to filter by Search for Fields, Category = [the Category selected] with filter input boxes next to each field.
They type in values, as they type it in you use the “Input’s value has changed” event to continuously build a List of Texts custom state. When they are ready to search all you need to do is fire off one search Search for Products, CombinedPropertes=[Custom state list](optional constraints for Price min and max), then do :each item's Supplier if you just want the supplier and not the actual products.
In the CombinedPropertes field it is crucial that the naming and formatting is standardized. So something like all lowercase fields and values would be best.
This will definitely need some refining but hopefully it will help us get started 
Thanks a lot for your detailed explanation. I have come up with the following structure, it is not solves yet but made a progress:
Data Type: Product
fields:
product_name: text
Data type: Company
company_name: text
company_sizeranges: List of Size Range(s)
Data type: Size Range
SizeRange_no: number
SizeRange_product: Product
SizeRange_company: Company
SizeRange_thickness: Numeric range
SizeRange_diameter: Numeric range
SizeRange_width: numeric range
SizeRange_length: numeric range
SizeRange_coating: numeric range
SizeRange_AvailableColors: text (to be chosen from an Option Set)
SizeRange_AvailableGrades: text (to be chosen from an Option Set)
Data Type: Inquiry
Inquiry_no: number
Inquiry_Lots: A list of Inquiry Lot(s)
Inquiry_client: company
Data type: Inquiry Lot
Lot_no: number
Lot_inquiry: Inquiry
Lot_Items: A list of Item
Lot_Product: Product
Data Type: Item
Item_no: number
Item_diameter: number
Item_thickness: number
Item_width: number
Item_length: number
Item_coating: number
Item_color: text
Item_AdditionalSpecs: text
When the company receives an inquiry it will input that with a form to the database. And the a repeating group within Inquiry_Details page must list all the producers that can produce these materials and sizes and specifications.
The issue is:
with the above structure;
I am creating redundant data slots for database where the value will be void. Also this is a very rigid structure limiting the flexibility.
Additionally, making a search for a RG for potential producers is very complex and I couldnt achieve to formulate it yet.
1 Like
There is nothing inherently wrong with this… but
this is the main problem hence me mentioning a fully dynamic field structure, not specifically hard coding field names.
I wouldn’t worry about storing inch and mm/meters
just store everything is mm, then convert the users inputted measurement to mm before you do searches.
users could also input in inches but the system just converts it mm on form submit.
this standardizes everything and makes the system a lot easier to work with and maintain and reduces fields.
you see this a lot with duration (minutes seconds) - just converted to seconds but displayed and entered differently by the user
