How to store different user's information in database?

Question

My first post :)

I just started learning Rails and have some doubts about database modeling. I am building a site for one soccer club and have many different user types. Each user can login to a site and have his own control panel. They also want that each user has separate part on site for registration. So, on my site I will have links "Register as a player" and "Register as a coach".

These would be my users on site and their attributes:

SuperAdmin (id, username, firstName, lastName, password)

Admin (id, username, firstName, lastName, password, is_enabled)

Coach (id, username, firstName, lastName, password, is_enabled, coachRole, salary, contractStarted, contractEnds, vacationDaysLeft)

Player (id, username, firstName, lastName, password, is_enables, salary, contactStarted, contractEnds, birthDate, height, weight, goals, assists, minutesPlayed, gamesPlayed)

What would be my options here?

OPTION 1

I was thinking about doing something like this:

create_table "users", force: true do |t|
    t.string   "username"
    t.string   "userRole"
    t.boolean  "is_enabled"
    t.string   "firstName"
    t.string   "lastName"
    t.string   "password"
    t.string   "coachRole"
    t.integer  "salary"
    t.datetime "contractStarted"
    t.datetime "contractEnds"
    t.integer  "vacationDaysLeft"
    t.datetime "birthDate"
    t.integer  "height"
    t.integer  "weight"
    t.integer  "goals"
    t.integer  "assists"
    t.integer  "minutesPlayed"
    t.integer  "gamesPlayed"
    t.datetime "created_at"
    t.datetime "updated_at"
end

Column userRole would have values SuperAdmin, Admin, Coach, Player. Depending on that role I can grant access to certain pages to those users. The problem is that this table looks huge and would have many null values.

For example, if I create Coach, row in table will have the following values:

Coach["username", "Coach", "is_enabled", "firstName", "lastName", "password", "coachRole", "salary", "contractStarted", "contractEnds", "vacationDaysLeft", null, null, null, null, null, null, null, "created_at", "updated_at"]

If I create Admin, he will have these values:

Admin["username", "Admin", "is_enabled", "firstName", "lastName", "password", null, null, null, null, null, null, null, null, null, null, null, null, "created_at", "updated_at"]

So, only player would really have some use of this approach.

OPTION 2

Second I was thinking to do was to create one 'basic' model User which will hold common information:

create_table "users", force: true do |t|
    t.string   "username"
    t.string   "userRole"
    t.boolean  "is_enabled"
    t.string   "firstName"
    t.string   "lastName"
    t.string   "password"
    t.string   "type"
end

And then for all other models to create new models:

class SuperAdmin < User
end

class Admin < User
end

class Coach < User
end

class Player < User
end

But then I've read that I would have the same problem because all those columns would again be in the same row for each user. Now I am really not sure how to handle this situation. I don't need any additional gems for authentication and authorization. I want to do it by myself to learn as much as possible.

I really appreciate your time in helping me to handle this. Thank you.

score 2 · Accepted Answer · answered Feb 13 '14 at 01:53

2

One solution could be store the common part in table ”users“. And create a table "properties". The properties table have "key", "value" columns. You can store arbitrary properties in the table properties, like "coachRole", "salary" and other properties you may want to add in the future(very possible).

Models relationship could be:

class User < ActiveRecord::Base 
  has_many :properties
end

class Porperty < ActiveRecord::Base 
  belongs_to :user
end

answered Feb 13 '14 at 01:53

lalameat

754
3
10

Thanks. There would be again some null fields so I was wondering, what do you think if I would make 2 tables like that. For example, CoachProperties and PlayerProperties. That way I can easily add another columns for each property and it's eadier to add new user roles. What do you think, would that be a bad design? – user3304086 Feb 13 '14 at 06:19
It should not have null fields. What I mean is in table properties, only 2 column you need to care about, "key", "value", the key could be "salary" or "coachRole", the value could be "10000" or "some role". If a user don't have "salary" property, then no such record in properties table. All columns in table properties could be "id", "key", "value", "user_id". "salary" is a part of the content of a record, not a column. – lalameat Feb 13 '14 at 06:28
Aha, I got it! So Property table will have only these columns: id, key and value. Then after each user registers I would create specific properties. The thing I don't understand is how will I display that on form for registration. If I will have form_for @user, how will those properties be handled? They will not be visible. – user3304086 Feb 13 '14 at 15:22
It is anther question not about DB design. I think it is usually handled by jquery or js. If user select a user type, then automatically show the related select. Also I think you might set "accepts_nested_attributes_for" in user model and "fields_for" in user form. Please google theirs usage. They are supported well in Rails. Anyway, if you think the DB design part is acceptable, could you accept the answer. – lalameat Feb 14 '14 at 02:48

How to store different user's information in database?

1 Answers1