How to correctly maintain database schema in rails?

Question

I have schema like this:

create_table "grades", force: :cascade do |t|
    t.integer "cls"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  create_table "post_grades", force: :cascade do |t|
    t.integer "post_id"
    t.integer "grade_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  create_table "posts", force: :cascade do |t|
    t.integer "user_id", null: false
    t.string "title"
    t.text "content"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

create_table "user_grades", force: :cascade do |t|
    t.integer "user_id"
    t.integer "grade_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

In this schema grades, posts and users all are related to each other.

So my first question: Is it correct way of doing this?

Suppose one of user added grade 4 (cls = 4) in his column and another user added same grade. Now I have same value of cls in grade table for two different grade ids.So is there any data redundancy in this schema?

Can you describe logic of your tables? Too hard to say something useful without any conceptual understanding — AntonTkachov, May 02 '18 at 21:04
User have many grades and can post posts in those grades. Grades can have many posts and a post can only be in one grade . A grade can have many users. Is it enough or need to elaborate more? — Vishal Goel, May 02 '18 at 21:20
Does `Grade` has any specific user like a creator/etc or they are all similar? — AntonTkachov, May 02 '18 at 21:25
What is `cls`? Yes, your response makes things a bit clear, but still need a bit more info to help you — AntonTkachov, May 02 '18 at 21:26
Yeah in this User have many roles like Admin, Teacher and Student in which only Admin and Teacher can post posts in grades. — Vishal Goel, May 02 '18 at 21:29

score 0 · Accepted Answer · answered May 02 '18 at 21:35

0

As I got your task, you can go with such solution:

grades -> no references (as it is right now)
user_grades (conventional way to name this table would be grades_users... alphabetical order and both in plural) -> user_id, grade_id (as it is right now)
You don't need post_grades. It's redundant
posts -> grade_id, user_id (if you need author)

Your schema.rb will look smth like:

create_table "users", force: :cascade do |t|
  ...
end

create_table "grades_users", force: :cascade do |t|
  t.integer "user_id"
  t.integer "grade_id"
  ...
end

create_table "grades", force: :cascade do |t|
  ...
end

create_table "posts", force: :cascade do |t|
  t.integer "user_id", null: false
  t.integer "grade_id", null: false
  ...
end

answered May 02 '18 at 21:35

AntonTkachov

1,784
1
10
29

Suppose one of user added in grade (cls: 4) in his column and another user added same grade. Now I have same value of cls in grade table for two different grade ids.So is this also data redundancy ? – Vishal Goel May 02 '18 at 22:00
No, it's not data redundancy, because following such approach you can end up that all duplicate data is redundancy. Do you need `Grade` to be created only with unique `cls` field? – AntonTkachov May 03 '18 at 17:05
No..for a particular User ..Grade is created only with unique cls field but of course different user can have same cls field as other User has.. – Vishal Goel May 03 '18 at 17:45
Then everything is all right. And, please, let's get back to your initial question. Right now it's already a new question. Have my answer helped you? If yes, then please mark it with a tick and up vote for future searches. Lost of thanks in advance. – AntonTkachov May 03 '18 at 17:50

How to correctly maintain database schema in rails?

1 Answers1