Need help with structure for Memo fields and independent join table.

[Phred]

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

[June7]

Why is the client insisting on all memo fields in one table?

Are these 'memos' standardized content that can be associated with multiple records from several tables? And can each record of the other tables have multiple associated memos?

A third option might be multiple 'junction' tables.

[rpeare]

I agree with june, having a 'master' memo table is kind of ridiculous. Let's say that you have a client, each client would have a memo field, now let's say each client had multiple orders, each order would have a memo as well (for example) in the context of browsing through a memo table it would mean absolutely nothing to the uninitiated viewer because you'd have to link in all the external tables for it to mean anything. Did your client explain why he/she/they want a centralized memo repository? What I would suggest is keep your memos on their respective tables but perhaps create a union query to put together all the memo fields from all the tables that are relevant to whatever search you're doing.

[Phred]

I agree with both of you. The client's brother-in-law is an old developer turned network consultant and a very sharp guy. He developed the Paradox system I am replacing. He was involved in the initial phases of the development and sent me requirements he wanted in the development. The centralized memo table was a part of the requirements. I didn't question it at the time. Now I can see the difficulties in the requirement. But since this concept was new to me I was thinking there might be benefits that I was missing. Apparently not. I have received the best support from this forum. To answer June7 questions:

I don't know.
No standardized content.
No related memos in other tables
There would be a junction table for almost every table.

Fred

[June7]

Does this requirement mirror the Paradox setup? How did 'sharp guy' handle that?

[Phred]

Well I have to chuckle at that. No it isn't mirrored in the Paradox. This development is far beyond the Paradox. However he is closely related to the Client, he was instrumental in selecting me for the development, he has been a helpful resource in other areas. I just happened to disagree on this one. And I need other opinions for when I have to argue my position and my reasoning for blowing it aside. And also there might be something I missed.

Thank you seems inadequate for all your help.

[June7]

Well, you have two votes here for no 'master' memo table, certainly if these are free data entry and not standardized content ('boiler plate').

Good Luck!