3
\$\begingroup\$

I've created a scenario below, in which a user can build a pizza and choose their toppings, then order their pizza:

require 'active_record'
require 'logger'

ActiveRecord::Base.establish_connection adapter: 'sqlite3', database: ':memory:'
ActiveRecord::Base.logger = Logger.new $stdout
ActiveSupport::LogSubscriber.colorize_logging = false

ActiveRecord::Schema.define do
  self.verbose = false

  create_table :pizzas do |t|
    t.string :name
  end

  create_table :pizzas_toppings_groups do |t|
    t.integer :pizza_id
    t.integer :toppings_group_id
  end

  create_table :toppings_groups do |t|
    t.string :name
    t.integer :user_id
  end

  create_table :toppings do |t|
    t.integer :toppings_group_id
    t.string :name
  end

  create_table :orders do |t|
    t.integer :user_id
  end

  create_table :ordered_pizzas do |t|
    t.integer :order_id
    t.string :name
  end

  create_table :ordered_pizza_toppings do |t|
    t.integer :ordered_pizza_id
    t.integer :topping_id
  end
end

class Pizza < ActiveRecord::Base
  has_many :pizzas_toppings_groups
  has_many :toppings_groups, through: :pizzas_toppings_groups
  has_many :toppings, through: :toppings_groups
end

class PizzasToppingsGroup < ActiveRecord::Base
  belongs_to :pizza
  belongs_to :toppings_group
end

class ToppingsGroup < ActiveRecord::Base
  belongs_to :user
  has_many :pizzas_toppings_groups
  has_many :pizzas, through: :pizzas_toppings_groups
  has_many :toppings

  validates :name, presence: true
end

class Topping < ActiveRecord::Base
  belongs_to :toppings_group

  validates :name, presence: true
end

class Order < ActiveRecord::Base
  belongs_to :user
  has_many :ordered_pizzas
end

class OrderedPizza < ActiveRecord::Base
  belongs_to :order

  has_many :ordered_pizza_toppings
  has_many :toppings, through: :ordered_pizza_toppings
end

class OrderedPizzaTopping < ActiveRecord::Base
  belongs_to :ordered_pizza
  belongs_to :topping
end

# admin defines pizzas and available toppings groups and related toppings

cheesy_pizza = Pizza.create(name: "cheeeeeeeese")

cheap_cheesy_group = cheesy_pizza.toppings_groups.create!(name: 'basic cheeses')
mozza = cheap_cheesy_group.toppings.create!(name: 'mozzarella')

fancy_cheesy_group = cheesy_pizza.toppings_groups.create(name: 'fancy cheeses')
goat = fancy_cheesy_group.toppings.create!(name: 'goat')
cheddar = fancy_cheesy_group.toppings.create!(name: 'cheddar')

# imagine there is a fancy user interface where the user can choose 
# toppings from topping groups, but certain groups
# have min/max limits on the toppings you can choose, etc etc
# for now here's a method that
# just takes in chosen toppings, and ties it to the ordered_pizza:
def create_order(pizza:, chosen_toppings:)
  Order.transaction do
    order = Order.create
    ordered_pizza = order.ordered_pizzas.create(name: pizza.name)
    ordered_pizza.toppings << chosen_toppings # imagine this line is lots of complicated validations
    order
  end
end

# user orders a pizza

jims_order = create_order(pizza: cheesy_pizza, chosen_toppings: [mozza, goat])

jims_order.reload

jims_order.ordered_pizzas

# for business reasons, some toppings are no longer available, and are removed.

mozza.destroy
goat.destroy

jims_order.reload
jims_order.ordered_pizzas.first.toppings

Historical integrity is now compromised! The toppings are missing on Jim's ordered pizza and all other ordered pizzas with those toppings.

A few questions:

  • Is this an ideal way to saves orders with their customized pizzas?
  • What's the best way to avoid this historical integrity problem?
\$\endgroup\$
2
\$\begingroup\$

Saving customized pizzas this way, looks OK but it also depends on how the rest of application is structured. I think your biggest concern here should be the historical data integrity you mentioned in your second question.

When it comes to data, the context they live in, helps you figure out how you should treat them. For example, when running a business like a pizza store with online ordering, toppings once available to customers can't just be "deleted". They can become unavailable, discontinued or something in these terms, but not deleted. You can't respond to customers that ask you to add pepperoni to their pizzas: "Oh, we deleted pepperoni!".

Once data hit production they will always be part of your application, despite how their status changes, so you have to treat them as if they will always be around.

This is somewhat a common issue and there are a few ways to solve it. I can mentioned two of them which I've used in the past.

1st Approach: Soft Data Deletion

The first approach, would be to go with some sort of soft delete policy for your data. You can find many posts across the internet about this technique and you can find many gems for ActiveRecord which could be used right out of the box. Practically, what you achieve with soft deletes, is that, when you're deleting data, you're not really deleting them, but you mark them as deleted so you can exclude them from your queries.

The most common ways to do this is either by adding a new column to your table that indicates a record has been deleted (e.g. deleted_at:datetime, deleted:boolean), or by adding a separate, mirror table in which "deleted" data will be moved (e.g. deleted_toppings). Both options have their pros and cons so it really depends on how the application is designed.

Keep in mind that soft delete may add complexity to your app. For example what will happen when you require Topping#name to be unique and you try to add a new topping with an identical name that already belongs to a soft-deleted topping?

2nd Approach: Separate model for customized ordered pizzas

The second approach would be to treat OrderedPizzaToppings in a completely different way. The idea here is to decouple OrderedPizzaTopping from PizzaTopping.

It could look like this:

class OrderedPizza < ActiveRecord::Base
  belongs_to :order

  has_many :ordered_pizza_toppings
end

class OrderedPizzaTopping < ActiveRecord::Base
  belongs_to :ordered_pizza
end

Every time an ordered pizza is created you will dump each Topping's data to a OrderedPizzaTopping instance and you'll save it. For example you could have something like this:

class OrderedPizzaTopping < ActiveRecord::Base
  belongs_to :ordered_pizza

  def self.from(topping = Topping.new)
    create!(data: topping.to_h)
  end
end

# ....

def create_order(pizza:, chosen_toppings:)
  Order.transaction do
    order = Order.create
    ordered_pizza = order.ordered_pizzas.create(name: pizza.name)
    ordered_pizza.toppings << chosen_toppings.map { |t| OrderPizzaTopping.from(t) }
    order
  end
end

If you noticed the only thing that OrderedPizzaTopping does is to store a hash version of PizzaTopping. Just a simple data structure containing name, description, price, image url, etc. Now, even if the PizzaTopping gets hard-deleted at some point, you'll always have the raw data which will allow you to represent the Order in any way you want. Optionally, you could store a reference to the original PizzaTopping in case it exists in your database.

Historical data integrity is not about deleted data only. What will happen if someone changes the price for a Topping that has been around for a year? All orders that include pizzas containing this topping will be affected. I think the keyword here is auditing. Auditing a model, means to log data changes throughout time.

\$\endgroup\$
  • \$\begingroup\$ Thanks for the extensive writeup. I know designing systems like this is always about tradeoffs. \$\endgroup\$ – Kori John Roys Dec 5 '16 at 14:42
  • \$\begingroup\$ @KoriJohnRoys yes, tradeoffs is the keyword here and are always depending on what you're willing to sacrifice. \$\endgroup\$ – Ioannis Tziligkakis Dec 5 '16 at 14:44
  • \$\begingroup\$ I think in my case it's worth it to link back to the original toppings, since I can see the client wanting to ask "how popular is pepperoni?" for example, which would be a harder question to answer with the second setup. \$\endgroup\$ – Kori John Roys Dec 5 '16 at 14:46
  • \$\begingroup\$ Also, price would be calculated once, at checkout, given coupon, delivery price, etc, then saved on Order, or likely tied to Order as a Payment or Debit record of some kind, so I don't see that being an issue. \$\endgroup\$ – Kori John Roys Dec 5 '16 at 14:48
  • \$\begingroup\$ The place I work for is fond of begin and end dates for records. This allows for multiple occurrences of the same thing with different date spans. Limited time toppings, and pizzas, can come back next year. For an allegory, see the McDonald's menu and its Shamrock Shake, which is available for a limited time in the U.S. around St. Patrick's Day. \$\endgroup\$ – Greg Burghardt Dec 5 '16 at 21:29
1
\$\begingroup\$

I'd say the best way to express this would be to add a boolean flag column to toppings which indicates whether the topping is currently available to be ordered.

The "semantic" meaning of deleting a row from a SQL database is that the data it contains no longer exists and/or will never be accessed again by the application.

The semantic way to hide certain data from the customer-facing side while preserving them on the business-facing side is to create a flag column under which queries can be scoped.

Note that you can use a domain/lookup table/enum/multiple booleans to express more complex ideas, e.g.

available => [anchovies, pepperoni]

seasonal => [lobster]

discontinued => [Soylent bars]

banned => [foie gras, cannabis oil]

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.