Rails: How do you handle dynamic attributes in the database ?

Question

If you come across classifieds websites like craigslist, oodle, kijiji,... everything is a classified, when you are posting a classified, they show different form fields depending upon the category one selects. For example, if you are posting a classified about Car, they show make, model, interiors, etc... How do you think data is handled in the database?

Do you think they are creating separate table for each type of classified/ or everything is one table "Classifieds"? If the data is handled using one table (STI) there must be columns for each and every scenario (type of classified) and all the fields might not be used for every classified.

What is the best way to design the ActiveRecord objects to handle to have similar classifieds website?

Answer 1

They likely have separate tables. While they are all classifieds and they are "categories" to the user, as you say the data is very different and I would suspect that they are stored separately because of that. There would be no real advantage (that I can think of) to storing them all in one big table when most of the time people will be querying an individual table. The disadvantages, as you say, would be all the columns for all types would need to be in that one table which would create a wide table with a lot of null data.

The categories themselves are pretty fixed so adding new ones is likely pretty rare. The other way you do it is to have defined properties associated to the one big table and templates of dynamic properties (Classified has many Properties). But that would probably not be good from a performance point of view.

Answer 2

There is undoubtedly a source specifying the basic schema for each type of Classified. If you're implementing such a system yourself, you may be aided by persisting the individual records with a schema-free persistence layer such a CouchDB.

Doing so means adding or removing attributes to a given schema type need not affect older records.