Database design for optional fields

Question

I've got 3 tables:

orders
line_items
products

They're setup as:

order has many products through line_items

This allows me to store in line_items such things as the product_id, quantity, price at time of purchase, discount, etc...

All is well up to this point.

What I'm looking to achieve:

I now need to have some products that have a user changeable status. Meaning that at some point in the future after an order has been processed, the purchased product status can be changed from one status to another.

The product table has a statusable boolean field that tracks whether said product supports a status.

The question:

Would I just add a status field in line_items? Only a small amount of the products will require a status so it feels like a waste but I'm not sure how else to approach this hurdle. My main concern being that I'll end up with a massive table as the application grows and specific products require extra optional fields.

Your order and line_item records should be immutable, in append-only tables, as should the copies of product for the orders. So if you must change something after the fact, I'd put it in separate table like product_status that you can insert status rows into at order creation time for products that can have a status. Make that table updateable so you can update the status and only the status later. It should be impossible for users to edit any other part of the order after the fact - they should have to append amendments instead. — Craig Ringer
Rationale: The order is a record of something that happened, not live state. So it should be immutable. Mistakes should be corrected with journaled amendments. — Craig Ringer
Thanks @CraigRinger for the explanation, would you mind answering this corollary. If an order has a state (i.e. shipped), should that not be a field in the orders table? — Vasseurth
@Vasseurth Well, you could use database column-level permissions to grant INSERT on all order columns but only grant UPDATE on the state column, I guess. Personally I'd use a side-table so I could keep orders append-only, but that's a design preference. Either way you should make sure no other columns can be changed. — Craig Ringer
Totally agree @CraigRinger. To be clear, you're suggesting a simple table for each post-purchase configurable product option and programmatically assigning the line_item.id to the option? — EasyCo

Richard Peck Richard Peck · Accepted Answer · 2014-08-18T10:42:06

There are two options for this:

Create a column in the join model

Create a separate "statuses" table, which you can use to create specific status updates

--

Attribute

I would personally create a column in the join model to support a status. Yes, it will require you to have that column for every line_item, but it will make the process much simpler, and will give extensibility without massive issues

You'll be best using one of the state machine gems (state_machine, aasm_state) to provide:

#app/models/line_item.rb
Class LineItem < ActiveRecord::Base
  include AASM

  aasm do
    state :active, :initial => true
    state :inactive

    event :activate do
      transitions :from => :inactive, :to => :active
    end

    event :deactivate do
      transitions :from => :active, :to => :inactive
    end
  end
end

This will give you the ability to directly affect the status of the line_item model

--

Associated Model

Alternatively, you may want to create a different table / model:

#app/models/line_item_status.rb
Class Status < ActiveRecord::Base
   #field id | line_item_id | status | created_at | updated_at
   belongs_to :line_item
end

#app/models/line_item.rb
Class LineItem < ActiveRecord::Base
   has_one :status
   delegate :status, to: :status #-> allows you to call @line_item.status
end

This will give you the ability to set the status for each product, making your data tables more efficient by only including a single status for each line_item

Database design for optional fields

1 Answers