1

I'm working on a legacy app - right now, we allow admins to generate forms with custom fields (they create the field, choose an input type, label, etc).

When the user fills out this custom form, all fields from that form are checked - if that field is not a column on the users table, we add it to the users table as a column.

For example, if an admin added a field called "flight arrival time", we would add a column called "flight_arrival_time" to the users table, and the User model would have an attribute called @user.flight_arrival_time.

What alternatives might there be to this current course of action? Is there a more efficient way of storing these values?

Here are some of the limitations:

We have tens of thousands of users ( I was told that storing these attributes in a different table and joining them would slow the system A LOT. We often have around 20 or so admins querying, importing, updating, and generally using the hell out of our system, which is already pretty slow under load. I wouldn't have the power to say "buy more {X} so we can be faster. ). I assume a join table (called something like user_attributes) would store the user_id, the attribute name, and the attribute value. If each user has an additional 15 attributes, and we have 100,000 users, how much slower will it be?

The storage must be easily query-able ( We use a dynamic search that allows the users to choose any column from the User model as a search field and find an inputted value ). Would you option allow easy queries (for instance, find all users whose attribute named "Flight Arrival Time" is tomorrow). Would this also become very slow?

I will experiment a bit, generate some of the proposed schema, generate 100,000 users and 20 attributes for each, and run some test queries to check execution times, but I'd like some input on where to start.

Thanks for your input.

Improve this question
1

3 Answers 3

Reset to default
2

not exactly an answer, but i think that this kind of app would benefit a lot from a document-oriented database / NOSQL system like mongoDB.

Such systems are schema-less by design.

To add my two cents, let users make dynamic changes on the schema seems a very dangerous option in an RDBMS environment to begin with. You could end up with tables with thousands of mostly empty columns, and rails would instantiate objects with thousands of methods on them. .. and what happens when you delete a column ?

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

NOSQL was the suggestion by the other developer for if/when we can find the time to integrate. The users are trusted (they buy and license the software to run their events, they wouldn't purposefully act with malicious intent. Though they might do something accidentally, they haven't yet through the years). When a field is deleted, it is left as a column - we don't delete that column. Each database supports one event, and generally doesn't involve adding more than 20 or so attributes.
1

In a long run with the approach you are following can make your database very slow. Your database size will grow as adding columns according to user behavior will leads to null values for other tuples.

It's better you use Document oriented databases. like mongodb, couchdb, cassandra etc.

Improve this answer

Comments

0

There's a gem for that. https://github.com/Liooo/dynabute

Also, this question is a duplication of

Rails: dynamic columns/attributes on models?

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.