Inserting foreign keys within a text field

[hatchback]

Hi guys

I've got a table - "Products" - in my database, with a text field - "Info" - which contains info about products.

Within this field I would like to have footnotes. To do this, I think the best way to do it is by putting numbers inside the text at the location of where I want the footnote to refer to. These numbers will actually be foreign keys to a table called 'Footnotes'.
I can then program the forms and reports to show any numbers as superscripts or whatever.

(Of course, if the user will actually want to insert a number into the text field which is NOT a reference to a footnote, I will have to make a workaround e.g. by making access put a symbol in front of the number, so access will know the number is just part of the text (and I will program the form to not show the symbol in front of the number)).

Does it make sense? Is it wrong to have foreign keys within a text field? I think if executed correctly, it should work perfectly.

What do you guys think?

Thanks in advance.

 

[hatchback]

Thanks Pat. Great to be here!

Let me be more clear: In the footnotes table, there will be a field for the "Name" of the footnote, which will not be the primary key, just a simple name for each footnote for the user to refer to. The primary key will indeed be an autonumber. I never intended for the user to be able to see (in the info field of the products table) the actual foreign keys. I intend to program the forms/reports to only show (in superscript) the "name" of the footnote, as you advised.

My question is, while this approach may not be perfect in terms of normalization, is there anything actually wrong with it? Why? Will it be too difficult to program in? Will it cause any problems? Will it just not work? Can anyone suggest a better solution? I've thought about it a long time, and I couldn't come up with a better way.

Thanks!

 

[hatchback]

Thanks Pat.

So you're telling me that if executed correctly (easier said than done), it would do the job? Or do you think that there are other more fundamental problems with this design?

I know it's not the ultimate in normalization, but there's nothing about it which is just wrong, is there?

 

[hatchback]

Putting the footnotes in the text is easy enough but as I said, they blend in with the text unless you delimit them somehow and that causes other problems.

Delimiting the numbers really isn't too tricky. What do you mean when you say it'll create problems?

Making it possible for the user to retrieve and manipulate the numbers doesn't seem to me to be impossible to do. For the project I'm doing it should only be anyway a very rare occurence. Of course it'll be giving a lot of responsibility to the user, but that's just a sacrifice I'll have to make.

Thanks for your advice and reassurances Pat. You're great!

 

[hatchback]

Sure, delimiting isn't a problem. Thanks again for your assistance, Pat. I'll go for it and see how it goes. Keep up the good work!