I'm just looking for some advice on a good way to handle the following situation:
I have a users table which contains a standardized row set (username, password, email, etc). However, each user can also have numerous settings and other various attributes linked to their account. These are not standardized and one user may have many more attributes than another.
Currently, I have two tables: users and settings in an a one-to-many setup, which I make possible via an INNER JOIN. With a query a little like this:
SELECT * FROM USERS INNER JOIN settings ON users.id=settings.id
The settings table consists of three rows: ID (which is a foreign key linking to the users table), attribute and value. These are pretty self-explanatory. You have an attribute name and a value for that attribute.
The problem here is that the above query returns the user tables column set (username, password, etc) with attribute and value as column names. So, for each settings row that belongs to the user, I'm currently getting a result row containing the users details (username, password, etc) along with two columns named attribute and value.
Essentially, what I'm looking to do is have each attribute name be returned as a column name. So, say for example two rows from the settings table were found for a particular user:
ID: 1 | attribute: type | value: 1
ID: 1 | attribute: role | value: 4
Rather than getting the following results:
ID: 1 | username: bob | email: firstname.lastname@example.org | attribute: type | value : 1
ID: 1 | username: bob | email: email@example.com | attribute: role | value : 4