Hello!

I am writing a addressbook module for my software right now. I have the database set up so far that it supports a very flexible address-book configuration.

I can create n-entries for every type I want. Type means here data like 'email', 'address', 'telephone' etc.

I have a table named 'contact_profiles'.

This only has two columns:

id           Primary key
date_created DATETIME

And then there is a table called contact_attributes. This one is a little more complex:

id       PK
#profile (Foreign key to contact_profiles.id)
type     VARCHAR describing the type of the entry (name, email, phone, fax, website, ...) I should probably change this to a SET later.
value    Text (containing the value for the attribute).

I can now link to these profiles, for example from my user's table. But from here I run into problems.

At the moment I would have to create a JOIN for each value that I want to retrieve. Is there a possibility to somehow create a View, that gives me a result with the type's as columns?

So right now I would get something like

#profile type    value
1        email   [email protected]
1        name    Sebastian Hoitz
1        website domain.tld

But it would be nice to get a result like this:

#profile email           name            website
1        [email protected] Sebastian Hoitz domain.tld

The reason I do not want to create the table layout like this initially is, that there might always be things to add and I want to be able to have multiple attributes of the same type.

So do you know if there is any possibility to convert this dynamically?

If you need a better description please let me know.

Thank you!