Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2012
    Posts
    3

    Question Alternative to EAV design in my case?

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what's wrong with creating a customized tailored table for each user based on the user's defined columns?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2012
    Posts
    3
    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?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by revg View Post
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jan 2012
    Posts
    3
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •