Allen Fair on 13 Dec 2005 02:59:08 -0000


[Date Prev] [Date Next] [Thread Prev] [Thread Next] [Date Index] [Thread Index]

Re: [PhillyOnRails] Rails and Database Design Issues

Cassius Rosenthal wrote:
I have a question inspired by Aaron's excellent talk this past Tuesday. Say I have a standard database-driven web app. Like any respectable database admin, I use postgresql, so I'm fond of defining the model logic in stored procedures (PL). Rails doesn't like this, because it is ignorant of the PL, and so the nifty Rails features often conflict with or supersede them. Rather than end up with two sets of model logics, I think it's easiest to use Rails and abstain from PL unless I need to address performance issues later in development. By doing this, I know that I'm losing the ability to easily transport the model logic to another language. For better or worse, I'll be stuck with Rails for these apps. That's fine for now.

Cool, another Postgres user! Rails is veru mysql-centric. Hard to believe that real programmers don't like data integrity and ACID complaiance... that's what a database is for!

There are two schools of thought concerning using stored procedures. I tend to favor putting all my business logic in one place in my code. Sometimes, a shared database, particularly from cross-platform applications, requires logic inside the databae.

The Ruby mentality here is to add metaprogramming "definitions" to your class to create higher-level code. Then you only need to override with code to the non-trivial implementations.


Here's my hypothetical: say you have two types of data: triangles and points (Cartesian). The triangles have three points each, but they're ordered. You can't tell from looking at a point whether it's the first, second, or third. There are a couple of ways to set this up in a database:

1)
triangles (id)
   has_one :first_point
   has_one :second_point
   has_one :third_point
points (id | triangle_id | place | x | y)
   belongs_to :triangle

2)
triangles (id | first_point_id | second_point_id | third_point_id)
   belongs_to :first_point
   belongs_to :second_point
   belongs_to :third_point
points (id | x | y)
   has_one :triangle

3)
triangles (id)
has_one :first_place
has_one :second_place
has_one :third_place
places (id | triangle_id | point_id | place)
belongs_to :triangle
belongs_to :point
points (id | x | y)
has_one :place
Normally I think I'd go with 3, in case I later wanted to use a view to move sets of points together or something. This method, which I'd be most comfortable with in postgresql, seems awkward in Rails, and doesn't look like it would be very efficient. Calling on @triangle_object.first_place.point.x (method 3) to find the x-coordinate seems like overkill compared to @triangle_object.first_point.x (method 1 or 2).

I haven't tried it, but I'm assuming that Rails will not be happy if I include triggers in the table definitions, because then the database would delete objects before Rails has a chance to, leaving Rails with missing objects scheduled for deletion. (A race condition where the database would always win?) So without views and triggers and PL functions, I don't know which method is best.

Ok, this is a rather abstract business, triangles and such. I can not infer what you want to do with them. A triangle has 3 sides, and you want to enforce that rule it seems. Perhaps you should consider abstracting your shape to a Polygon, and subclassing that.

create table places (id ..., x ..., y ...., ....);
create table polygon(id..., ....);
create table triangle (id ..., ); -- alternatively
create table vertices (id ..., polygon_id ...., place_id ...);

class Place < ActiveRecord::Base
  has_one :polygon
end

class Polygon < ActiveRecord::Base
  has_many :places

  def number_of_sides (sides)
    @sides = sides
  end

  def self.create (*places)
    raise "shape must have #{@sides} sides" if places.size != @sides
    t = Ploygon.new(...)
    places.each do |p|
      Vertex.new ( :polygon_id=>t.id, :place_id=>p.id)
    end
  end
end

class Triange < Polygon
  number_of_sides 3
end

class Vertex < ActiveRecord::Base
  belongs_to :Polygon
end



What is the most Rails-y way to set up the database? The model definitions? What kinds of things should I be considering when I sit down to plan something like this? Anybody else run into similar considerations?

The major things I deal with when using Postgres is are sequences instead of the mysql autoincrement feature. Also, a lot of my tables utilize multiple columns in my primary keys. This means I have to override the find*() methods and do a lot more find_by_sql than I would hope.

I just answered thiss off the top of my head, so it may not be accurate yet. I hope if give you a direction to start in.

Allen :-)
_______________________________________________
talk mailing list
talk@phillyonrails.org
http://lists.phillyonrails.org/mailman/listinfo/talk