10 Replies Latest reply on Jun 16, 2015 9:50 AM by mingram

    Adding categories in a library, is a new table with relationship needed to propery sort by category?

    mingram

      Currently each library item has key words, but we want to produce a list of all the items in the library by category first, then by key words. For added complication, some items in the library may belong to more than one category.  What is the best approach for this issue?

       

      Thanks for your help.

        • 1. Re: Adding categories in a library, is a new table with relationship needed to propery sort by category?
          mark_baum

          Hi Mel,

           

          What do you mean by "produce a list..."? 

           

          * Are you intending to produce separate lists of library items by finding on the category first, then finding by key word?

           

          * Or are you intending to sort and group your library items by category and then key word?

           

          Given that you are intending to associate more than one category with a given library item  -- and typically multiple key words are associated with a single record as well -- sorting and grouping doesn't make sense.

           

          You could simply have a text field for Category formatted as a checkbox using a value list containing your categories. That value list could use the values in another table, or simply use a static list of values.

           

          Or you could do it relationally, creating a Category table with one record per category and an ItemCategory join table with one record per item-and-category.

           

          It really depends on what other uses you'll make of the data. Performing a find on the text field will be faster because the text field can be indexed.  Any find on related records involves unstored values.

           

          But the relational approach might allow you to modify categories more flexibly.

           

          Hope this helps --

           

          Mark

          • 2. Re: Adding categories in a library, is a new table with relationship needed to propery sort by category?
            mingram

            Thank you. I think the best route is to figure out the relational table.  Is there a video tutorial on relational tables?

            • 3. Re: Adding categories in a library, is a new table with relationship needed to propery sort by category?
              mark_baum

              Hi Mel,

               

              I'm going to move this thread to the general Discussion area, where more people are likely to chime in.

               

              I can't remember if you've worked through the Learn training, but this page introduces the concept of relationships:

               

              Learn Goal 4 - Part 3: Learn about relationships

               

              In this case, however, you want to create a join table which adds a level of complexity.

               

              What's your reasoning for choosing to approach this relationally instead of with a local text field? Let's see how other people advise you after I move the thread.

               

              Cheers --

               

              Mark

              • 4. Re: Adding categories in a library, is a new table with relationship needed to propery sort by category?
                Extensitech

                My (humble?) recommendation.

                 

                Assuming you already have the table of library items, which I'll call LIB.

                 

                Create a new table called CAT (categories,,, incidentally, I just recently renamed by standard categories table "tags", since that seems to be understandable to more users, and not imply more than it is).

                 

                In CAT create, in addition to the usual primary key and so forth, a field for the LIBkey and a text field for the category name.

                 

                Create a relationship between LIB and CAT (LIB_CAT?) with the create and delete options checked on the CAT side.

                 

                Create a portal on the LIB layout showing a portal of LIB_CAT, with the category name field showing. I'd recommend making the category name auto-complete based on previously entered values, to prevent users from accidentally entering many variations of the same word, but still allow them to create new categories as needed.

                 

                Your report ("produce a list") should be based on the LIB_CAT, sorted and/or grouped by category name, but showing the LIB name.

                 

                hth

                 

                Chris Cain

                Extenstiech

                • 5. Re: Adding categories in a library, is a new table with relationship needed to propery sort by category?
                  erolst

                  mingram wrote:

                  For added complication, some items in the library may belong to more than one category. What is the best approach for this issue?

                  This is not really an issue, rather a typical scenario. The best approach to it is to not store keywords or categories in a field in the item, but to use a child (or join) table for each – where the difference is that the latter one “joins“ the LibraryItems table to a dedicated table of Categories or Keywords, respectively.

                   

                  See the attached sample to see how this can be implemented.

                   

                  If you want to combine the lists into one PDF, you could use the Append to PDF feature. If you want to have a single combined list, you should investigate the Virtual List technique.

                   

                  But then, what exactly is the difference between a keyword and a category in your solution? You could also use a single table for both types and use a … well, type field to distinguish between them, and thus be able to find/list/summarize either or both.

                  • 6. Re: Adding categories in a library, is a new table with relationship needed to propery sort by category?
                    erolst

                    mark_baum wrote:

                    Performing a find on the text field will be faster because the text field can be indexed.  Any find on related records involves unstored values.

                     

                    Mark –

                     

                    this is not correct.

                    • 7. Re: Adding categories in a library, is a new table with relationship needed to propery sort by category?
                      mark_baum

                      Yikes, I stand corrected. Still, I'm not completely convinced that a join table is needed here. It depends on what the goals of the solution are, the volume of the data, how the solution is expected to evolve, etc. -- and we haven't heard from Mel about that yet. But a join table is the more elegant and flexible option.

                       

                      Mel, can you tell us what problem this solution is intended to solve, and how you expect it to be used?

                      • 8. Re: Adding categories in a library, is a new table with relationship needed to propery sort by category?
                        Extensitech

                        Seems like we've given Mel three different ways, and they depend on the details.

                         

                        Entering the text directly in the library item is certainly the easiest to set up (and apparently what Mel has now). The downside is that in a list of library items, the one item can't appear in more than one group, or more than one place in the sort order, when it has more than one category. (The virtual list method, which Erolst referred to, could be used to get around that, but it's a method that I couldn't describe in a paragraph or two, although I use it a lot.) Searching might be marginally quicker (not sure on that, really) but you'd have to be pretty careful with your search parameters, so that "main account" doesn't find "accounting, Maine, sales". Also, there's little control over data entry, so you're libel to get entries like "sales", "selling", "sls" and so forth.

                         

                        The way I described is a 1:M, where one library item may have many records in the categories table. This lets you search just one table away on a stored field. It also allows you to control data entry with auto-complete, and it allows you to run your sorting and grouping reports on the Categories table, where each category assigned to each library item is its own record. The downside here is that you can't edit a category name and have the name change for everything in that category. Also, the categories themselves may have attributes, like notes, a definition of the category, or other attributes.

                         

                        Erolst's way goes further by creating a join table between library items and categories (I'll call the table "theJoin" because nothing else comes to mind). LIB has a 1:M relationship to theJoin, and the Join has a M:1 relationship to CAT, creating a M:M relationship. A possible downside is that you'll be doing your searches in a stored field two tables away (again, may not be that big a deal). It has the added advantage that now there's a table of Categories where a category can be renamed, have its own attributes, etc. If there's any chance of the categories needing attributes of their own, this is definitely the way to go.

                         

                        Incidentally, our current base system uses Erolst's method for Categories. I'm working on an overhaul of the base to take advantage of FM14 and other new items, and we've decided to move to the method I proposed, and just call the table "tags". We found that although the separate table for Category names was technically correct, none of our clients ever used additional attributes for them, and that selecting categories, creating new categories on the fly, and so forth, although fairly easy, puzzled users who just wanted to mark a record as being in a category.

                         

                        Anyhow, I think it comes down to how important those categories are in this system, beyond just searching, sorting and grouping.

                         

                        Chris Cain

                        Extensitech

                        • 9. Re: Adding categories in a library, is a new table with relationship needed to propery sort by category?
                          electon

                          erolst wrote:

                           

                           

                          But then, what exactly is the difference between a keyword and a category in your solution? You could also use a single table for both types and use a … well, type field to distinguish between them, and thus be able to find/list/summarize either or both.

                          It's a good point but I think having a separate keywords field will simplify searching later on.

                          Less need for complicated constrains end extends.

                          • 10. Re: Adding categories in a library, is a new table with relationship needed to propery sort by category?
                            mingram

                            So we have an e-library database with a collection of reports, articles, presentations (referenced files, all converted to PDF and in a container field for easy viewing) that we have collected since 1999. We eventually want to put this resource on our website but currently there is a one field - key wd - which has a mishmash of words that relate to the document being viewed/current record.  What we want is to have categories to sort the collection properly, and then a limited list of key words for basic searches - I say limited list so that it's in a drop down and more manageable/uniform.  There are 15 categories.  For example, the category Energy may have several reports, and key words could be anything including oil, pipeline, environment, government, etc -- but we also have a category for Environment so ideally I would like to be able to have all 15 categories as a checkbox so that a record may belong to one or more categories, key words can still be applied and a report can be generated by category or by key word.  We will need to define our key words so that they are not redundant or similar to the categories.

                             

                            Thanks so much for your help.