1 Reply Latest reply on Jun 25, 2015 1:55 PM by philmodjunk

    Is there a reason to create two tables in this situation?



      Is there a reason to create two tables in this situation?


      Quick design question for you database veterans out there (Please note that I'm super-new to databases and FMPro. I've been teaching myself as I go for the past 2 months.):

      I've been chugging away at creating a combination research inventory and CRM solution for my freelance writing work, and I'm needing a bit of design advice at this point.

      Does it make sense to divide a table of seemingly like (or downright identical) fields into two tables for any reason?

      Case in point: On my CRM end I have a "Clients" contact table which is populated with people who hire me to write. (Individuals, editors, marketing directors, etc.) These contacts are connected to an "Organization" table (what company/publication do they work for, if any?) and a "ClientCorrespondenceLog" table (which tracks all calls & emails to/from them.)

      On my research end I have a similar contacts table called "Sources", which is populated with people, publications, and websites that provide research for my writing. These, in turn, are connected to a "ResearchNotes" table (contains the actual research), and their own "SourceCorrespondenceLog" table (again, to keep track of all contact I've had with them—calls & emails for people; pages and URLs for publications.)

      So my question is: should I have two correspondence log tables, or separate tables for sources and clients for that matter? In my mind, I'm being very neat and organized by having two correspondence logs that are 100% identical, and by keeping my clients and my sources separate. But I understand that this might not be good design (FMPro could separate the two quite easily with the LogID field.)

      The only structural justification I could come up with, in my layperson's mind, is that it'll keep the tables—especially the correspondence logs—from getting too big. But what is 'too big?' Will FMPro slow down at some point when there are 15,000 entries in the "correspondence log" and its join table?

      And for that matter, is this something that might bite me in the butt down the road as I'm finalizing my database? (I do plan to have a "to do" notification home page, which will include urgent callbacks, writing stage tasklists, and whatnot. And once I'm finished with the writing half of the database, I plan to start making a CRM and project pipeline system for my photography business...and I'll want all of the "to do"s from both halves to funnel into that notification home page.)

      Thanks much!

        • 1. Re: Is there a reason to create two tables in this situation?

          In most cases, a unified table works better. In a few cases, it makes no real difference.

          The key detail here is that you can keep your different groups of records in the same table and still work with them in discrete groups. All you need is a field in the table that identifies group membership and you can use a number of methods to keep the groups separate. Where you gain a significant advantage in putting the data all in one table is when you get records that need to be a member of both groups. If, for example, A "source" for one project turns into a "client" for another project, you only need maintain one contact record on that person even though you use that data as a source in one place and as a client in the other.