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

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

I think, what you're asking, is how do you stop the following from happening: 1 | woodwork_rating | intermediate; 1 | woodwork_rating | advanced; 2 | woodowork_rating | advanced; The problem being that there is now no way to tell which woodwork_rating to use for user 1. Is that right? — Dave
– Dave, Commented Nov 23, 2010 at 16:56

Larry Lustig · Accepted Answer · 2010-11-23 17:04:11Z

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.

answered Nov 23, 2010 at 17:04

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dianovich Over a year ago

This makes sense although I realise my question was misleading. Not all of the user fields will be ratings, although I think this would work if I change UserRatingList to UserMetaList or something and UserRatings to UserMeta.

Larry Lustig Over a year ago

Yes, it's always more complex in practice than in theory. Exactly how I would structure the more complex database depends on what that additional meta-data looked like (and you might choose a somewhat different design than I would). The important thing to take away from my answer (if anything) is to avoid the design that adds a column to some table for every new rating. Instead, use a design that adds a row to one or more tales for each new rating type (or meta type) you create.

Sebastian Flückiger · Accepted Answer · 2012-01-29 09:57:23Z

1

i would like to rely on 'mike' s last answer. i am facing this exact problem. i am creating a network with the following types

Enterprise {name, mail, adress, etc ...}
Employee {name, mail, branch, jobdescription, etc ...}
Individual {name, mail, surname, age, town, etc...}

how about:

Table Users

userID (autoincr)
userType
mailadress
password
...

Table Usertypes

typeID
typeName
typeDescr

Table Enterprises

entID (autoincr)
userID
field 1
field 2
...

Table Employee

empID (autoincr)
userID
...

Table Individuals

indID (autoincr)
userID
...

does this make sense to you?

answered Jan 29, 2012 at 9:57

Sebastian Flückiger

5,5658 gold badges37 silver badges72 bronze badges

Comments

mike · Accepted Answer · 2010-11-24 18:08:15Z

0

Are all the fields going to be "ratings" with the same datatype? If so I like Larry Lustig's solution.

Or could there be unrelated fields with different data types? dates, strings, decimals, integers? If so, the first solution of having 1 to 1 related tables that join with Users is OK. As long as you don't need to dynamically add new fields at run time.

answered Nov 24, 2010 at 18:08

mike

761 bronze badge

Collectives™ on Stack Overflow

Database design: different fields for multiple user types

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related