Wednesday, March 7, 2012

Interview Question : Database Structure

Really cool when interviewer asks you about the database structure.
As I am a ROR developer it helped me to think the way rails is designed. I mean first think of associations you will need, this will help to add the database table which will be used by an application.

So now real question was ::
There is this Admin user and he wants to dynamically add fields/ Questions to the user's form. So what will be the database structure of the application for this specific feature.

So first the most came in my mind was Railcasts complex forms episode 73 - 75 (As I am a big fan of Ryan Bates) and also episode 196 and 197 nested model form.

After some quick responses the final answer was which I thought :
That there needs to be users table and of-course these will have multiple roles based on which Admin role user can add dynamic fields to the form. For not mixing it with users table included 1 more table profiles. This will be
class User < ActiveRecord::Base 
  # Associations 
  has_one :profile
end
class Profile < ActiveRecord::Base 
  # Associations 
  belongs_to :user
  has_many :answers
  accepts_nested_attributes_for :answers
end
So there will be 1 more table profiles with user_id as foreign_key which will be a reference for users table. As far it was just a base but the real solution starts here.

About the fields or questions. The interviewer asked me that the field which he wants to add can be as a question.
Like "What is your last name?"/ "Last Name". So I named it as label? Yes. So here came new table which will be independent of questions and it will have 'id' and 'label' so just 2 columns.
Now the answers part this will be shifted to 1 new table which can be named as "answers". But each question will have only 1 answer per user.
class Question < ActiveRecord::Base 
  # Associations 
  has_many :answers
end
class Answer < ActiveRecord::Base 
  # Associations 
  belongs_to :profile
  belongs_to :question
end
So this will have profile_id(for user reference), question_id(questions) and answer(for users to be filled)

So the final structure which was created was
users (which will be independent) id, name and other fields like password....etc
---+--------+
 id | Name |
---+--------+

roles (which will be just to know the role of the user) with has_and_belongs_to_many :users
---+--------+
 id | Name |
---+--------+

profiles (which will be just to keep this extra information separate) this table can be optional and users table can be used directly for reference, but generally extra information needs to be kept separate considering the security.
---+----------+-------------------+
 id | user_id | Other Columns |
---+----------+-------------------+

questions (individual table) id, label/question
---+--------------------+
 id | Label/Question |
---+--------------------+

answers (which will be the table for storing the input values given by users against the questions added by Admin user) profile_id/user_id, question_id, answer
---+------------------------+---------------+---------+
 id | profile_id/user_id | question_id | answer |
---+------------------------+---------------+---------+

4 comments:

  1. Nice way to provide detailed ans in interview.. :)

    ReplyDelete
  2. I got related content, please go through link below
    http://stackoverflow.com/questions/574570/add-fields-to-activerecord-model-dynamically-in-rails-2-2-2

    ReplyDelete
    Replies
    1. Thanks Anil, that really helped alot...

      Delete