I'm rebuilding an application that will have eight(8) different tables that will require multiple memo field records for each record in each table. My question is: would it be better (meaning safer in dealing with memo fields) to have 8 separate child memo tables (one for each parent table) or one(1) memo table with 2 separate fields (corresponding to 2 unique fields in each parent table) linking a specific memo record to it's parent table and record? This sounds a bit confusing even to me.
Here's the idea:
Parent Table 1 could have record 1 with a unique field entry "F000001" and it's child table could have 25 records linked to "F000001"
Parent Table 2 could have record 1 with a unique field entry "F000001" and it's child table could have 50 records linked to "F000001"
And so on for each of the 8 tables. This is the 8 child tables for 8 parent tables scenario. One child table for each parent table.
Or:
Parent table 1 could have record 1 with a unique field entry "F000001" and a table identifying field entry "01" with 25 child records linked to "01"-"F000001".
Parent table 2 could have record 1 with a unique field entry "F000001" and a table identifying field entry "02" with 50 child records linked to "02"-"F000001"
And so on for each of the 8 tables. This would be the 1 child table for ALL PARENT tables scenario.
Right now the application has only one parent table with a linked child table for memos and there are almost 300,000 memo records.
I realize the second method is more efficient and if I were dealing with any other field type except memo it would be the obvious choice, but with 8 parent tables there are going to be a lot more memo records and I'm just not sure whether or not spreading them out over different tables will be safer than lumping all of them in one table.
Thanks for your input.
Jim Smith
Windows 7 OS and v11 possibly switching to v12
Here's the idea:
Parent Table 1 could have record 1 with a unique field entry "F000001" and it's child table could have 25 records linked to "F000001"
Parent Table 2 could have record 1 with a unique field entry "F000001" and it's child table could have 50 records linked to "F000001"
And so on for each of the 8 tables. This is the 8 child tables for 8 parent tables scenario. One child table for each parent table.
Or:
Parent table 1 could have record 1 with a unique field entry "F000001" and a table identifying field entry "01" with 25 child records linked to "01"-"F000001".
Parent table 2 could have record 1 with a unique field entry "F000001" and a table identifying field entry "02" with 50 child records linked to "02"-"F000001"
And so on for each of the 8 tables. This would be the 1 child table for ALL PARENT tables scenario.
Right now the application has only one parent table with a linked child table for memos and there are almost 300,000 memo records.
I realize the second method is more efficient and if I were dealing with any other field type except memo it would be the obvious choice, but with 8 parent tables there are going to be a lot more memo records and I'm just not sure whether or not spreading them out over different tables will be safer than lumping all of them in one table.
Thanks for your input.
Jim Smith
Windows 7 OS and v11 possibly switching to v12
Comment