dynamic searchable fields, best practice?

I have a Lexicon model, and I want user to be able to create dynamic feature to every lexicon.

And I have a complicate search interface that let user search on every single feature (including the dynamic ones) belonged to Lexicon model.

I could have used a serialized text field to save all the dynamic information if they are not for searching.

In case I want to let user search on all fields, I have created a DynamicField Model to hold all dynamically created features.

But imagine I have 1,000,000,000 lexicon, and if one create a dynamic feature for every lexicon, this will result creating 1,000,000,000 rows in DynamicField model.

So the sql search function will become quite inefficient while a lot of dynamic features created.

Is there a better solution for this situation?

Which way should I take?

1. searching for a better db design for dynamic fields

2. try to tuning mysql(add cache fields, add index ...) with current db design

hi,

im trying to integrate a "ajax-chat" into my app. within the page where the user post a chatmessage runs a periodically_call_remote to update the message. now, before this happens, the user needs to create a "room" first. now my idea was that the controller could just do a replace_html, which would b a div in the application-layout. but this doesnt work. if im looged in from a different browser, i dont see that update. clearly, im doing something "different here". how would u do that, or any advise?

thx

boblu wrote:

I have a Lexicon model, and I want user to be able to create dynamic feature to every lexicon.

And I have a complicate search interface that let user search on every single feature (including the dynamic ones) belonged to Lexicon model.

I could have used a serialized text field to save all the dynamic information if they are not for searching.

You might still want to. Perhaps you can write a routine that writes SQL queries that search through the YAML in the serialized field. It's annoying, perhaps, but better than the alternative.

In case I want to let user search on all fields, I have created a DynamicField Model to hold all dynamically created features.

But imagine I have 1,000,000,000 lexicon, and if one create a dynamic feature for every lexicon, this will result creating 1,000,000,000 rows in DynamicField model.

So the sql search function will become quite inefficient while a lot of dynamic features created.

That doesn't follow. You'd have to search through a billion lexicon regardless. Joining another table shouldn't decrease the efficiency too much, I think

Is there a better solution for this situation?

Which way should I take?

1. searching for a better db design for dynamic fields

2. try to tuning mysql(add cache fields, add index ...) with current db design

Your best bet here is probably 3: use a non-SQL database such as CouchDB or MongoDB. If you do decide to stick with an SQL database, I recommend PostgreSQL -- it will probably deal better with your big tables.

Best,

Thank you for the detailed reply.

I will have a research on mongoDB. And I am wonderring is there a way to integrate mongoDB with rails?

[Please quote when replying. This is a mailing list, not just a Web forum.]

boblu wrote:

Thank you for the detailed reply.

I will have a research on mongoDB. And I am wonderring is there a way to integrate mongoDB with rails?

Yes.

Best,

Tom Tom wrote:

hi,

im trying to integrate a "ajax-chat" into my app. within the page where the user post a chatmessage runs a periodically_call_remote to update the message. now, before this happens, the user needs to create a "room" first. now my idea was that the controller could just do a replace_html, which would b a div in the application-layout. but this doesnt work. if im looged in from a different browser, i dont see that update. clearly, im doing something "different here". how would u do that, or any advise?

thx

Stop hijacking other people's threads! Learn to create a new topic for new questions.

Best,