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.)