Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
"So having a foreign key to HelpText table in Products table makes no sense. " Why not? You could join the HelpText table with Product, couldn't you? –  FrustratedWithFormsDesigner Feb 12 '13 at 19:10
    
My thinking was because a record in Products table doesn't tie to a record in HelpText table. A record in HelpText table would tie it to a column in Products table, not to a row. Does that make any sense or am I way off? –  user19100 Feb 13 '13 at 16:55
    
"A record in HelpText table would tie it to a column in Products table" ... ok now I'm even more confused. Could you post an example of the tables, as they currently are? –  FrustratedWithFormsDesigner Feb 13 '13 at 17:03
    
I'm sorry...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. Because MedicalCode in Product table may have strict format issues or confuses users, we have help text that shows up on entry form in the application. So we would have a record in HelpText table with description for MedicalCode. We may have another field description for other tables into HelpText table. –  user19100 Feb 13 '13 at 17:11
    
So... I'm still not sure why you can't have a help_text_id in Products that points you to the correct record in the HelpText table. HelpText only stores an ID and text, so why not just create a new record for every place where you need it? –  FrustratedWithFormsDesigner Feb 13 '13 at 18:14
show 3 more comments

2 Answers

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

share|improve this answer
    
Thank you for taking a look at this.I will attempt to put some data below. Please note that help text will not really going to include validation rules since those will be handled in code but just to illustrate the structure, I have used the validation rules. The help text does not go with individual product or vendor record yet, it belogs to the whole field "MedicalCode". Imagine a data input screen, and when a person who is entering the data will be able to see the help text to help them enter data and this help text is not for the end-user who are doctors, pharmacist, or patients. –  user19100 Feb 14 '13 at 19:59
    
<h1>Products</h1> ID | Name | MedicalCode ----------------------------- 1 | red pills | 445631-XS 2 | blue pill | 043333-PP Vendors ID | Name | VendorCode ----------------------------- 1 | VendorABC | DDA123 2 | VendorBBD | ZZE222 HelpText ID | Name | Description -------------------------------------------- 1 | MedicalCode | Format must start with 6 numeric number, then dash, followed by letters 2 | VendorCode | Format of VendorCode must start with 3 letters followed by 3 numbers –  user19100 Feb 14 '13 at 20:03
    
ok I tried modifying the comment but I hadn't had any luck :-( –  user19100 Feb 14 '13 at 20:15
    
@user19100: You can't have formatted code/text in a comment. –  FrustratedWithFormsDesigner Feb 14 '13 at 20:30
    
@user19100: Ok, so just change product_help_text to have a medical_code column instead of a product_id column (and rename it to medcode_help_text if you want). The basic idea should still work fine. I might update this answer if I have a chance later. –  FrustratedWithFormsDesigner Feb 14 '13 at 20:32
add comment

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)
);
share|improve this answer
    
I had seen these type of tables before but didn't know you would use "references" keyword for those, or what those were called before. Thanks!! My explanation of the problem was premature...I apologize. It's hard to explain :-( How would you design the tables if a record in help_info tie to an entire column in product table? As an example, If product table has a column for MedicalCode among others like Name, etc. This MedicalCode has a corresponding record in help_info which includes text that explains how users should enter data for this field. –  user19100 Feb 13 '13 at 17:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.