For both types there are common information to keep track of, such as Brand Name, Active Substance, Supplier, Manufacturer, etc., but for PPC there are quite a lot of other data to, that it specific for PPC and that is not valid for OC. I have two options how to handle this data a) in one table for both PPC and OC, where the specific information for PPC is kept in a tab or popover, or b) two separate tables.
I would recommend using a single table. This again leaves you with two options: Put all fields that are required for both types into that table, or just the fields common to both, and create an additional attributes table.
You'll possibly hear different opinions about this …
To make it a bit more complicated, I want to have one table with the most common PPC and OC on the market for the horticultural sector, where the user then, in a separate table, choses which chemicals they use in their company.
Wouldn't simply a flag be sufficient (regardless whether you use two tables or just one)? Then filter on that flag, be it in a relationship, a portal filter or a Find request.
I've used one table and I've used two tables (one-to-one relationship), so that the most used data is in the first table and extraneous data needed by only one "type" is in another.
What I mean by "most used" is: for searching, sorting, reporting/printing. It can be faster to not have large tables (columns and/or rows). A layout could have the related data in field (entry even allowed with only one record matching) for entry and/or reporting.
I think the "hurdles" (if there are any) are the Interface and Scripting, not the data. One-to-one relationship for this kind of data is a piece of cake.
If, on the other hand, you try something like EAV
you might have more work (entry and reporting).
Thank you. Now you gave me a third option in how to use two tables. One with common/most used data and a second with attributes/less used data.
The EAV-alternative (common data and attributes data) is an interesting alternative. It might also solve some problems for me, where I can store data that is common for all countries in one table, and attributes that are country specific are stored in one table. I would like to distribute "tables" with tables of common ly used plant protection chemicals together with the FM solution. The company can then fill in the attributes for the chemicals that they used and that are country specific. Also the solution will take less space, which is important for iPad-solutions.