problem with a validation table

I have a validation table Person Type with fields type_id and type. In the type filed开发者_如何学JAVA I need to store the following values: PubCrsAtt, Health Professional, Trainee, Qualified Trainer, Customer.

My questions:

How can I add Graduation Date to Qualified Trainer and Job Title to Health Professional within the validation table?

How can I distinguish Health Professional as a subcategory of Trainee within the validation table?

Any help will be much appreciated Zan

You can't, unless you want to be shooting yourself long before the end of the project. Instead, you should (I'd say must!) add two more columns: Graduation_Date and Job_Title. So your table would look something like this:

field 1   field 2   field 3      field 4
typeid     type    grad_date    job_title

You might also want to add a field for the person's name. (And maybe the person's office telephone, office location, and anything else interesting that describes one single person.)

To add Health_Professional as a subcategory of Trainee (or of any other type), you should add yet another field, call it subtype:

field 1   field 2  field 3   field 4      field 5
typeid     type    subtype  grad_date    job_title

To understand why it has to be this way, google 'database normalization'.