My first Rails app: choosing the database schema (newbie here)

rubyonrails-talk
1

Hi there people.

After lot of reading regarding how Rails works (controllers,models,views,routing,templating etc) and doing some tutorial apps, I’ve decided to start building my own application. It will be a real-world app that my and the other members of my team will use.

It will be a project management & collaboration app, crafted to fit exactly our needs: We have an online store running so we want to have a place where we will be able to post TO-DOs, ideas & messages. Now let’s get to the point: These are the database tables as of now (I’m ommiting the ideas & messages tables since they’re similar to the todos):

[ users ]

[ sections ]

[ subsections ]

[ todos ]

The main point is that first of all will be sections, for example “Site design”, “Marketing & Promotion”, “Checkout process” etc. I might call this table Projects instead, not sure about that yet.

Now, a section may (or may not) have one or more subsections. For example the “Site design” section may have a “Footer” subsection and a “Logo” subsection. Inside these subsections (or if there aren’t any, straight inside the section) there will be to-do lists, messages & ideas.

I hope you get the point by now. Well, now comes the important part, the associations. These are my initial thoughts which I think are kinda off, since there’s a problem if I decide to use a seperate table for the subsections, because the to-dos etc maybe also in a section. So this is my question for now: should I have a separate table for subsections or should I do it with one table only (sections)? In other words, I want to implement a “nested sections” system.

[ sections ] has_many: [ subsections ]

[ subsections ] belongs_to: [sections]

After I solve this issue, the other tables relations would be like this:

[ sections ] (or subsections) has_many: todos

[ todos ] belongs_to: sections

2

Just to be more specific: I want to have parent and child “Sections”. A section can be a parent section (top-level section) or a child of another section, or a child of another child section. Then, to-dos, messages & ideas can belong either to a parent or to a child category.

For example, consider this hierarchy:

Site design (parent section, doesn’t belong anywhere)

-Footer design

----Footer links styling

----Social media bookmarks

-Header design

----Logo design

----Menu design

Advertising & Promotion

-Social media advertising

----Twitter

----Facebook

-Offline advertisting

etc…

Looking at this table, I think it would be more flexible to have all the sections in one table and maybe use a “Parent_id” column in the same table. Am I missing something?

Thanks in advance.

3

Just to be more specific: I want to have parent and child "Sections". A section can be a parent section (top-level section) or a child of another section, or a child of another child section. Then, to-dos, messages & ideas can belong either to a parent or to a child category.

For example, consider this hierarchy:

Site design (parent section, doesn't belong anywhere) -Footer design ----Footer links styling ----Social media bookmarks -Header design ----Logo design ----Menu design Advertising & Promotion -Social media advertising ----Twitter ----Facebook -Offline advertisting etc...

Looking at this table, I think it would be more flexible to have all the sections in one table and maybe use a "Parent_id" column in the same table. Am I missing something?

Google for rails self referential and you will find many links, rails casts and so on that use this technique. It is very common.

Colin

4

since there’s a problem if I decide to use a seperate table for the subsections, because the to-dos etc maybe also in a section. So this is my question for now: should I have a separate table for subsections or should I do it with one table only (sections)? In other words, I want to implement a “nested sections” system.

Colin’s suggestion for using “self referential” techniques is definitely recommended. Additionally, from your description it sounds like you could possibly have "to do"s and other items associated with with a range of objects in our site. If you do go down this path, you will want to investigate Rails’ polymorphic relations. This would allow you to have "to do"s associated with sections, subsections, articles, orders, accounts, etc. I’ve used polymorphism to make one “picture” model available for association with users, articles, posts, products, etc. and it works pretty well.

Don