3 Replies Latest reply on May 10, 2011 9:39 AM by philmodjunk

    Defining Effective/Fast Relationships



      Defining Effective/Fast Relationships


      I started this topic in another thread, but it is divulging from the original post so I will continue it here. In the original thread we were discussing the importance of defining ID fields as Number and using an auto-enter serial ( e.g. 1 or 112 ) and whether defining the field as text and using the format 'Table15' ( e.g. Contact1 or Project112 ) would cause any problems or slow the system down. The original thread was: look if there are related records

      While renaming some fields this morning, to ensure a consistent naming contingency throughout my solutions, I realised why I setup the ID's in the 'Table15' format.

      I have a notes table which occurs repeatedly on the relationship graph as a convenient way of attaching notes (as related records) to various other tables... e.g. Contact_Notes, Project_Notes, etc... I have the field Notes::_id_Owner to create the relationship between the notes and the various other tables. All my ID fields are defined as text (so FM shouldn't get confused as it is doing Text = Text comparisons - and so far with 1000s of records it is working as intended, just not sure whether there would be any speed benefits) in the format mentioned above...

      If I used just a number in the ID field, Project 100 and Contact 100 would end up sharing notes. Ideally (if nobody can see any significant problems/speed repercussions of the way it is currently implemented) I would like to keep the current format so I don't have to change ALL the records in my db - however if the changes are worthwhile I could easily add another field to the Notes table (and wherever else required) and use this new field to decide whether this note belongs to a Contact or a Project. 

        • 1. Re: Defining Effective/Fast Relationships

          If I used just a number in the ID field, Project 100 and Contact 100 would end up sharing notes.

          I'm afraid I don't get that. From your post, you are using just a number and thus Project 100 and contact 100 should match to each other if you have defined a relationship linking by those fields (But why would you match a project number field to a contact number field in a relationship?). The fact that you have entered this number into a field of type text doesn't affect that issue one way or the other.

          I've never run any kind of test to compare the relative "speed" of relationships based on text fields vs. relationships based on number fields. If someone has, I'd be interested to see the results of such a test. I'd be suprised, though if there is a signficant difference for databases of Moderate size, but could easily be wrong here.

          • 2. Re: Defining Effective/Fast Relationships

            Maybe I didn't explain very well... My notes table has a field called _id_Owner and then I use the following relationships:

            Contact::id = Contactnotes::_id_Owner

            Project::id = Projectnotes::_id_Owner

            Where Contactnotes and Projectnotes are both occurrences of the same notes table. This allows me to quickly add multiple notes to any part of the system as required. Sounds like it won't be/cause an issue - so I'll carry on as planned!

            • 3. Re: Defining Effective/Fast Relationships

              Yes, but the fact that you use text instead of number makes no obvious difference in which records match to which here. Which made your statement that you couldn't use fields of type number here because then "Project 100 and Contact 100 would end up sharing notes" a bit confusing, and it still is.

              I prefer number data types for serial number fields as it makes it easier to avoid issues down the road when I work with the database. When I return to the DB 6 months later, I may not remember nor notice that the fields are of type text instead of number and then encounter problems when I reference the field with expressions or define new relationships while assuming that the fields are of type number rather than when they are of type text.

              Say, I want to display related records in a portal, sorted so that the most recently created record is listed at the top of the portal. If I've already defined the field to be of type number, I can sort the portal by the ID number field in descending order. If it is of type text, this doesn't work. This is just one of many little differences that you have to deal with if you use text, that you avoid completely if you stick with number type fields.