Help understand: closing a card, creates closure entity vs updates cards.closed_at column

[greenfork]

In Fizzy, the design about "card properties" is that a separate entity is created whenever an action is taken on a card. Closing a card creates a closure entity, gilding a card creates a goldness entity, etc. This "entity approach" helps with putting additional data into entities, in Fizzy it is often a single field user_id (closed by whom) but sometimes just the bare-bones entity without additional fields is created such as goldness.

I'm afraid to do this design for two reasons:

1. When filtering through the cards table and using these properties, it requires a LEFT JOIN. With so many fields, there could be performance issues because after 4-6 JOINs the SQL planner refuses to guess the most optimal order and just uses the naive order of these joins.

2. To load a single card with full data, it requires X queries, one for each property. Isn't it too much? Example from Card:

  scope :preloaded, -> { with_users.preload(:column, :tags, :steps, :closure, :goldness, :activity_spike, :image_attachment, reactions: :reacter, board: [ :entropy, :columns ], not_now: [ :user ]).with_rich_text_description_and_embeds }

this requires 15 separate queries if I count it right.

The alternative approach is to add fields into the cards table such as closed_at, closed_by etc. It avoids performance issues outlined above but clutters the cards table.

Could you explain the design decisions and performance caveats behind this approach? Thank you.

[greenfork]

I looked at the number of queries for opening a card. When I open a card, I see from development log this:

Started GET "/897362094/cards/2"
Completed 200 OK in 189ms (Views: 148.6ms | ActiveRecord: 11.9ms (41 queries, 1 cached) | GC: 6.9ms)
Started GET "/897362094/prompts/boards/03fkdvnnv8em9ceb2epqnfepn/users"
Started GET "/897362094/users/03fkdvnngcnqb5wwirni02nfd/avatar"
Started POST "/897362094/cards/2/reading"
Started GET "/897362094/cards/2/column/edit"
Started GET "/897362094/cards/2/watch"
Completed 200 OK in 193ms (Views: 30.5ms | ActiveRecord: 3.8ms (12 queries, 0 cached) | GC: 2.4ms)
Completed 302 Found in 271ms (ActiveRecord: 1.0ms (4 queries, 0 cached) | GC: 1.6ms)
Started GET "/897362094/cards/2/pin"
Completed 200 OK in 370ms (Views: 5.2ms | ActiveRecord: 23.5ms (14 queries, 0 cached) | GC: 1.6ms)
Completed 200 OK in 403ms (Views: 19.0ms | ActiveRecord: 2.6ms (9 queries, 0 cached) | GC: 0.9ms)
Completed 200 OK in 306ms (Views: 12.6ms | ActiveRecord: 3.1ms (11 queries, 0 cached) | GC: 0.0ms)
Completed 200 OK in 56ms (Views: 7.4ms | ActiveRecord: 2.0ms (9 queries, 0 cached) | GC: 0.0ms)
Started GET "/897362094/cards/2/assignments/new"
Completed 200 OK in 64ms (Views: 22.8ms | ActiveRecord: 4.1ms (16 queries, 0 cached) | GC: 0.0ms)

It is 41+12+4+14+9+11+9+16 = 116 queries total. Coming from a background of avoiding N+1 queries because the database could be in a different location and could have some ping time, this looks rather excessive.

[ajaychandrak2002-bit]

i need a break for today ..... please drop a mail today by morning, if incase of problem

[ajaychandrak2002-bit]

i have some medical issue please coordinate with sharma ,or else you can drop me a mail for it