Database design: different fields for multiple user types

Question

I am building an application using MySQL 5.0.77 that contains

a) different user types (e.g. carpenter, mechanic, ..., plumber)
b) different input fields for each user type, e.g. user selects carpenter and is presented with fields pertaining to that profession, where the fields for each profession are different

My thinking is along the lines of:

Table: users
user_id
user_name

Table: carpentry
user_id
woodwork_rating
metalwork_rating

Table: plumbing
user_id
central_heating_rating
bathroom_rating

And so on...

This does not seem very good though since I could potentially end up with lots of tables and users existing in multiple tables with different fields.

I quite like the idea of a metatags table (like we see in Wordpress) so that each users field entry is stored, e.g.

Table: user_info
user_id
field
value

So we would have for example

1 | woodwork_rating | intermediate
1 | metalwork_rating | advanced
2 | woodowork_rating | advanced

My question is, how would you structure a database that has multiple fields for multiple users for which each user only fills in one category of the available fields?

Thanks

Answer 1

Table Users:

 UserID: Autoinc PRIMARY KEY
 (More user data columns here)
 UserType: CHAR(5)

Table UserTypes

 UserType: CHAR(5) PRIMARY KEY
 Description: VARCHAR(50)

Table UserRatingList

 UserRatingCode: CHAR(5) PRIMARY KEY
 UserType: CHAR(5) REFERENCES UserTypes
 Description: VARCHAR(50)

Table UserRatings

 UserID: INTEGER PRIMARY KEY / REFERENCES Users
 UserRatingCode: CHAR(5) PRIMARY KEY / REFERENCES UserRatingList
 Rating: INTEGER (or whatever you prefer)

The table UserRatingList establishes the pattern of ratings that can be applied to each user type. UserRatings contains the actual ratings. I use CHAR(5) to provider readable codes without having to join in the Description fields, but you can change them to INTEGER if you want.

This structure can also be adapted to allow each user to have multiple types; simply create an addition UserTypeLinks table with UserID and UserType.