Hey Guys,

I am in a situation where I have a web application that must support the ability for users to create their own data structure and user defined columns. I am using MySQL as a database backend.

For example. The user in the system decides he needs to store information about video games. So he creates a structure called VideoGame and then defines the necessary columns as

ID - Number
Name - String
Difficulty - Number
GeoLocation - Lat,Lng
Available - Boolean
DateCreated - DateTime
DateModified - DateTime

Now he needs the ability to insert rows based on this data structure, as well as update rows, delete rows, and run queries on it.

However, some other user in the system might store information regarding something totally different like parking lot locations with a whole different set of attributes. There is no way of me knowing what a user might decide to store.

I really want to stay away from EAV design because it's brutal to maintain and if I get a ton of records in the tables then I don't think it will be scalable.

I know one solution could be to store the all of these data structures and rows as xml or json in a table, but the problem with that is I can't query on that either.

Do I have any choice other than using the EAV model for a situation like this? I have heard of NoSQL databases like MongoDB, but have no experience with that so I am not sure if that would be a solution or not.

Any advise would be appreciated.

Thanks

what's wrong with creating a customized tailored table for each user based on the user's defined columns?

hmmmm. so you mean programatically create a new table for each data store they create? Should I be worried about having too many tables in my database or is that a non issue?

Odds are that you'll run out of disk long before you exceed the number of tables that MySQL can handle.

-PatP

Ya... It looks like I might just have to go with the EAV model and build some kind of auto indexing feature. A lot of cloud storage web apps manage to do it and scale it, but I was hoping there might be a better way.