Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Assume a tool where one can create an useraccount, then store 'fields' for that account. Each field can contain zero or more 'sub-fields' and each of those can contain zero or more strings.

I wonder how a proffesional developer would design a database structure for such a use case.

My approach would be:

  • Create a table for each user
  • Add two rows, fields and id
  • store a big JSON object in the first row, which contains every subfield and its strings

But somehow i think that this won't be the best way to solve this and i'm having a giant brain fart.

I would greatly appreciate any suggestions.

share|improve this question
    
I won't speak for other professional developers, but I try to avoid that kind of use case. Letting end users essentially design their own databases has always caused immense pain and suffering. –  Mike Sherrill 'Cat Recall' Jul 8 '13 at 15:50
    
I would agree @MikeSherrill'Catcall', but consider a tool like, for example, a todo-app where you can not only create simple tasks but also different lists and subtasks. –  user2422960 Jul 9 '13 at 6:15
    
A to-do application doesn't require a user-defined database. It just requires user-supplied data. –  Mike Sherrill 'Cat Recall' Jul 9 '13 at 11:16

2 Answers 2

I did a presentation about alternative ways of defining "extensible" relational database schema at the Percona Live MySQL Conference & Expo in April 2013. Here's a link to my slides:

http://www.slideshare.net/billkarwin/extensible-data-modeling

share|improve this answer
1  
Good slide show. My favorite was slide #2 illustrating ALTER TABLE –  RolandoMySQLDBA Jul 8 '13 at 21:15

Why not have just one table, with a key for the user?

custom_user_attributes
----------------------
  id (PK)
  user_id (FK to user_accounts.id)
  attribute_type_id (FK to attribute_types.id)
  attribute_value

attribute_types
---------------
  id (PK)
  name

Each "field" is just an attribute_type. Strings can be stored in custom_user_attributes.attribute_value.

However, there are still some questions I have:

  • The 'fields' you speak of: are they known ahead of time, or do users create their own custom fields?

  • Do you need to track the relationships between fields and subfields in the database, or is that something that only applies on the UI, and you will let your code handle that?

share|improve this answer
    
Your last two questions would be up to the user to decide. The concepts you just described would suffice. +1 !!! –  RolandoMySQLDBA Jul 8 '13 at 22:25
    
The fields are created by the user and are further extandable in the future. I'd say that the relationship can be handled at UI side, yes. –  user2422960 Jul 9 '13 at 6:19
    
I implemented your concept and it works very well, thank you so far. But i have to admit that i don't understand how to add a 'subfield' –  user2422960 Jul 9 '13 at 7:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.