Access 2007 SQLServer 2008 r2.
Every screen/table has a Memo field. The client would like to have all memo fields located in a separate table called dbo_MasterMemo for illustration. The MasterMemo actual MEMO field would be a NVARCHAR(MAX).
Each screen/table has its own primary key. The structure would look somewhat like this:
The above works for One Property joined to One Memo. But it breaks down when you need 20 different tables linked to the one Memo table. It doesn't account for multiple tables. You could create the structure multiple over for every table containing a Memo field. That seems inefficient. You could have a join that has the Table Name, Primary Key, Memo PrimaryKey as below.
Both of these solutions have problems. The second solution seems better to me than the first.
I am looking for advice on how to structure this so it is efficient and correct. Is there a better way.
I have never created this type of table structure before. If the above isn't clear please ask questions, or point me in a direction. I would rather do it once right.
Thanks,
Fred