4 Replies Latest reply on Sep 8, 2014 10:43 AM by grouper

    create new table question

    grouper

      Title

      create new table question

      Post

      Hi,

      I have a table with a growing number of fields (>100) up to now. I plan on adding about 200 more fields on it.

      This, however would make the editing of the table&fields somewhat cumbersome, and i 'm also concerned that it may slow the db down. I 'd like to add the new fields in a separate table, that behaves like an extension to the parent table (each record from the new table is related to only one record on the parent one and vice versa). How should i set the relationships and IDs?

      thanks

       

        • 1. Re: create new table question
          philmodjunk

          each record from the new table is related to only one record on the parent one and vice versa

          This is not very likely to produce an improvement in database performance except for in a few cases.

          Having over 300 fields raises the possible issue that you actually may need a related table that is NOT linked in a one to one relationship where multiple records replaces the multiple fields currently in use.

          • 2. Re: create new table question
            grouper

            Thanks

            • 3. Re: create new table question
              FentonJones

              Yes, that's getting to be quite a few fields. Though I don't think its "too many" fields for a table.

              But if you want to, it is possible to have two tables. The relationship would be based on "from" being the "serial" ID of the "main" table, with "[✓] Allow creation of records in this table via this relationship". Then all you need is at least one field from the second table on the first's layout; when you enter anything in it, the new matching record in the second table will be created, with the matching ID; which will NOT be a "serial" ID (in the second table; it can have a separate "serial" ID of its own, if you want).

               

              Otherwise (if you'd do the above), you could have a script to test for the existing match in the second table, and create a record if not. This could be attached to a "go there" button, so no one has to really think about it. Then you wouldn't need any visible field from table two. Script to do so:

              If [ not IsEmpty (relationship::ID) ]
                  Go to relationship [ table 2; layout of table 2 ]
              Else
                  Set Variable [ $origID ; serial ID ]
                  Go to Layout [ table 2 layout ]
                  New Record/Request
                  Set Field [ ID_orig; $origID ]
                  Commit Records // to see any field showing on layout from table 1 (like critical name)
                  Go to Field [ whichever ]
              End If

              • 4. Re: create new table question
                grouper

                great reply Fenton, it's been very helpful.

                thanks a lot!