How to design data about a column can belong to the whole table?

Question

I'm not a DBA so I apologize for lack of knowledge. Here's my situation...Say I have several tables. Products, Customers, Vendors, etc.

Our application may have a help text for some of the data in those tables. Products may hold a column "SpecNumber" which needs to be in a specific format that may need a help text to display to the user, Customers table may hold another column, and so forth. Our application will need a way to maintain this list of help text so keeping it as one table with maybe ID, helpText, etc. May have other columns if we need to keep other information about the field in the future.

Now, how do I go about querying this help text? In an application where I display a list of products, for "SpecNumber", I want to show this text once on the form. So having a foreign key to HelpText table in Products table makes no sense.

---

Products table may have Id, Name, VendorId, MedicalCode. (sample data may be Id=1, Name='MedicineABC', VendorId=12 (foreignkey to vendor table), MedicalCode='123ABC3232') HelpText table may have Id, description.

Products
--------
  ID
  Name
  VendorID
  MedicalCode

HelpText
--------
  ID
  Description

Answer 1

Given the tables you have (I'm assuming this structure):

Products
--------
  ID
  Name
  VendorID
  MedicalCode

HelpText
--------
  ID
  Description

I'd suggest adding 2 new tables:

product_help_text
-----------------
  product_id
  help_Text_id

help_text_types
---------------
  id
  description

And changing HelpText:

HelpText
--------
  ID
  Description
  HelpText_type_id

So now, you could have some data like this:

Products
ID | Name      | MedicalCode
-----------------------------
1  | red pills | 445631-XS3
2  | blue pill | 043-PPLE-A

HelpText
ID  |  TypeID  |  Description
--------------------------------------------
1   | 1        | Take 3 times / day w.water
2   | 3        | Verify patient's health card
3   | 2        | check for other drug reactions

helpText_Types
ID  | Description
-----------------
1   | For the patient
2   | For the doctor
3   | for the pharmacist

product_help_text
product_id  |  help_text_id
---------------------------
1           |  1  
1           |  3
2           |  1
2           |  2

product_help_text tells you which text goes with which product, and you can use the help text types to control which text gets seen where (for patient, pharmacist, doctor, drug sales rep, etc...).

Answer 2

Your customers and vendors should probably be in the same table actually, or have the same parent table.

Use junction tables between your entities and the help_info table:

create table help_info (
  id serial primary key,
  description text
);

create table product_help_info (
  product_id int references products(id),
  help_info_id int references help_info(id),
  primary key (product_id, help_info_id)
);

create table customer_help_info (
  customer_id int references customers(id),
  help_info_id int references help_info(id),
  primary key (customer_id, help_info_id)
);