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

best practicesdatabase-design

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

Best Answer

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...).

Related Question