[SOLVED] Rails: create database structure for an archive


I have a general architectural question concerning the design of part of a database. I would like to implement an archive for an educational entity and have a form field, where I can query the archive and get a certain subset of entries. Everything started with only an archive of articles and lectures. I treated both as similar and used one table named resources with columns for all the data I needed in both cases. This way I could write in my Rails app something like:

Resources.find(:all).where('title LIKE ?', "%#{params[:searchTitle]}%")

and get all the resources with a certain string in its title.

Now I would like to extend the archive to comprehend a wider set of resources containing documentation of educational or research activities. I will have items as well as what you could call collections and sub collections. Items would be things like sheets, texts, images, magazines, books, music, video, drawings, models, maps. Collections would be something like courses, lectures, exercises, projects, exhibitions. A course can have multiple exercises and lectures. An exercise can have multiple projects, etc. But there might be projects without an exercise or course. I might choose to add keywords to the resources to describe content.

A query should result in a view with all related entries of single items and collections in one list.

I would usually create a table for each resource in order to have as few repeating data as possible. But I usually have one table as entry point for my queries and would have now 14 resources tables + join tables + tables such as authors. How should I set up the structure of such a database and in case I will have to create single tables for each resource: How do I query 14 tables and display the result in one list.

Thank you in advance.


in your controller action method,

resources1 = Resource1.where("title LIKE ?", "%#{params[:searchTitle]}%")
resources2 = Resource2.where("title LIKE ?", "%#{params[:searchTitle]}%")
resources3 = Resource3.where("title LIKE ?", "%#{params[:searchTitle]}%")
@total_results = resources1 + resources2 + resources3

Answered By – Bondhan Sarker

Answer Checked By – Mary Flores (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.