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.