3 Replies Latest reply on May 9, 2016 8:27 AM by jonasmn

    Help about database design

    jonasmn

      I working on a db where the company for legal reasons have to have lists of the chemical substances that they are using. In this case, there are two different types of substances that the companies are using: plant protection chemicals (PPC) and other chemicals (OC). 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.

       

      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. With one table for PPC and another for OC this would be done in two steps, one for the PPC and one for the OC. If I have PPC and OC in only one table, this step could also be done in one table.

       

      It does not necessarily mean that it is a bad thing that creating a list of chemicals used in the company is done in two steps, since the lists (PPC and OC respectively) would most likely be performed by different members of staff.

       

       

      What is the most practical and user friendly? I would appreciate some comments on this.

       

       

      Regards

      Jonas Möller Nielsen

      Sweden

        • 1. Re: Help about database design
          erolst

          jonasmn wrote:

          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 …

           

          jonasmn wrote:

          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.

          • 2. Re: Help about database design
            beverly

            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).

             

            beverly

            • 3. Re: Help about database design
              jonasmn

              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.