In my application I need a member list with custom columns. So I create a members table with only the ID. The member_attributes table contains the name and the type (e.g. 'Name', 'string' or 'Birthday', 'date'). In the third table I have the foreign key of members table (member_id), the foreign key for the attribute table (member_attribute_id) and the value of the attribute.
members: id, timestamps
member_attributes: id, name, type, timestamps
member_attribute_values: member_id, member_attribute_id, value
First question: is this the correct method to achieve this. It's working so far.
And second question is how to select all attributes, even if they are empty. I tried a right join, but that does not work:
SELECT * FROM member_attributes
RIGHT JOIN member_attribute_values on (member_attribute_values.member_attribute_id = member_attributes.id)
RIGHT JOIN members on (members.id = member_attribute_values.member_id)
Check this fiddle: http://sqlfiddle.com/#!9/7e097b/1
You can achieve this with the Cross join and Sub-Query
SELECT * FROM (SELECT members.id as member_id, attributes.id as attributesid, name, type FROM attributes, members) as t
left join member_attribute_values as mav on t.member_id = mav.member_id and
t.attributesid = mav.member_attribute_id
order by t.member_id
SQL Fiddler: http://sqlfiddle.com/#!9/3799f/9