Front end adaptable tables-- is it done, and how? Please help.

I have a question about database / model layer design, I'm wondering if anyone can comment. I'm thinking of using Symfony as my framework but I'm still wonding if it's appropriate. I am developing a website and I want it to be highly abstract at the database level. I've been searching around but I'm not sure what to call it or even if anyone has done it. I'm hoping an example of what I mean will help someone at least point me to some useful articles or get some useful comments.

The example:
I am developing a website with different forums in different rooms. For each room there is an administrator and that administrator will be deciding on the fly what profile information he/she wants people to enter about themselves, in addition to the usual basic info. The type of information in each extended field can be a string, an integer, or a date.

My database solution:
I've opted out of simply having a text field with YAML definitions for the extended variables because that will prevent me from indexing on them properly. I don't want to have a different table per forum unless I can create/remove tables on the fly as forums are created and I couldn't find any examples of dynamic table creation so I'm not thinking of that as a solution right now.

I've chosen the following table structure:

users
- userid
- first name
- last name
- email address
- account ID

users_string_fields
- sfieldid
- userid
- fieldName
- value (varchar 255)

users_int_fields
- ifieldid
- userid
- fieldName
- value (integer)

users_date_fields
- dfieldid
- userid
- fieldname
- value (datetime)


I'll note that I'm a bit concerned about the performance.. To show a single user profile I will need a join on four tables. Can anyone think of a better way? What I would really like is to be able to overload the database field and have the indexes work on the overloaded field! Can anyone provide comments on A) direct thoughts about my design, or B) articles that discuss this kind of dynamic use of tables. Also, as a side thought, if anyone knows if Symfony would or would not be a good framework for this kind of layout please comment.

Thanks!

You can run CREATE TABLE and DELETE TABLE queries just like any other, but you're right that you don't want to go down this route.

As for your tables, you're about as normalized as you can get, evident by the number of joins you'll have to do to get the data. Not necessarily a bad thing, though.

Another option is to have a single table with three separate columns for strings, integers and dates. Then you've got some wasted space but an easier query. Up to you...

Won't you need some more tables, though? If I'm following correctly, admins will be able to create dynamic fields. Won't you need a table listing the fields that were created and relating them back to a specific forum_id?

---John Holmes...

I guess what I'm trying to ask is if it's normal to have several small tables that join together.. I haven't worked on a lot of databases, but joining four tables to show one profile seems like a lot and I'm wondering if it could slow down processing quite a bit. Especially if I have that kind of thing happening all over the site.

How does a person assess whether there will be too many joins for a single operation and how their processing speed will be when all is said and done? I'm worried about getting all done, only to realize I need some monster server to run my database and it needs a redesign.



Yeah there will be something like:

Forum:
- Forumid
- Title

ForumMembers:
- Memberid
- Userid
- Forumid
- Role

Etc etc.. But I just wanted to use the first four tables as an example because it didn't seem normal to be to have 'extended fields' each as a separate table row of a different type in that manner but I couldn't think of a better way of doing it.

There will basically be various small tables requiring many joins. and I was concerned that might be a bad thing.

No, it won't be. So long as you're keeping the tables properly indexed and only joining on columns that are indexed, you'll be fine. This is what databases are designed to do.

---John Holmes...

Ok John, I'll go for it! Thanks.

Incidentally, say I was going for the single table with name-integer-varchar-date fields and there was only one of the integer-varchar-date assigned on each row... Do you know if mysql optimizes away the empty space, or is there space wasted on every row?

Wasted space on every row, I think... ask in the MySQL forum to be sure. Space is cheap, though... performance is what matters, IMO.

---John Holmes...

If anyone happens upon this post, apparently the model of which I am speaking is called the Entity-Attribute-Value model, or EAV for short. It is not generally recommended but sometimes it's necessary in my opinion. It must be getting more and more popular with websites getting more and more dynamic. The model is also called 'key-value pairing'.

It gives you a lot more flexibility then the standard relational model can provide. But because it's like fitting a round peg in a square hole, you loose a lot of the automatic checking and integrity protection that the database gives you.