Hello everyone I would like to create an app to build fertilization plans like the one you see in the picture below.
How would you organize the database?
I had thought of something like:
IDPLANS (UniqueID)
IDCUSTOMER (Ref type)
DATE
CULTURE (Enum)
VARIETY (enumlist)
DATE OF INTERVENTION 1
PRODUCT1 (enum)
DOSAGE1 (number)
INTERVENTION DATE 2
PRODUCT2
DOSAGE 2
DATE OF INTERVENTION 3
…
my problem is that:
- The number of interventions is variable
- How do I divide Radical interventions from Foliar interventions?
Thanks to anyone who helps me understand how to structure it better.
1 Like
This sounds like a typical case of item-detail tables architecture.
https://support.google.com/appsheet/answer/10101311?hl=en
You can either have separate child tables for Radical and Foliar or have one with a category column.
This depends on how you want to further process your data.
2 Likes
thanks a lot for the advice. what do you mean by “This depends on how you want to further process your data”? any advice or example to better understand?
I would also have another doubt. It is very clear how the parent-child tables work but, when a user has to create a new fertilization plan, he should do it all in a single form. While in this way he will have to add a new line to the child table each time and re-enter all the data such as customer, colture, variety etc … How can I make a single form to fill it out?
and how can I insert more products in an intervention (enumlist ??), and how to associate the relative dosages? all in a single entry line or should I divide the products into multiple lines?
To be honest I do not understand your business well enough to give you specific examples.
In general one table verses two tables affect how you implement further processing because if you need to process two different kinds of transactions together then you need to perform joins if you have two separate tables whereas if you only need to process only one kind at one time then having to deal with only one table may be more advantageous in terms of performance and simpler code.
If your data size is not very large, it may have no significant impact after all. I would think it also depends on the underlying data store.
The design also affects how users interact with the application because AppSheet is not very flexible in its UX. I guess if both types of fertilization plan need to be entered at one go, I would choose the one table approach.
Reporting is another aspect that is influenced by your table design. I find AppSheet somewhat clumsy at joining tables and it is usually easier to deal with less tables.
1 Like
