Cassius Rosenthal on 15 Dec 2005 03:47:22 -0000


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

Re: [PhillyOnRails] Rails and Database Design Issues 

Thank you for your input, Allen.  I commented below:

Allen Fair wrote:
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.
Could you give an example of metaprogramming definitions, and how this would apply to using a stored procedure?

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.
Ooops: triangles and points are used in my hypothetical above because they are abstract and familiar. I'm not looking for a solution to a specific ontology, but rather general advice regarding Rails-ified object/relation mapping.

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

This abstraction to polygons is interesting, but I'm more interested in the case where we have a reusable data type, in this case vertices, and a parent, in this case polygons, where the vertices are different in a specific way that can't be determined by the data itself, in this case the order of the vertices (first, second, third). Here's another example, to better illustrate the general case: Say we have a parent table 'racks' and a child table 'balls' and we want each rack to have specific balls like a cue ball, a one ball, an eight ball, etc. Each ball has data like its position and whether it's in the pocket or not, etc. In postgresql, I'd be tempted to make 'balls' a datatype, but feel like that would be bad to do in Rails. So instead we have a couple of choices.
In the database we could have:
racks(id, cueball_id, oneball_id, . . .)
balls(id, position, in_pocket)
--- or ---
racks(id)
balls(id, ball_type, position, in_pocket)
--- or ---
racks(id)
ball_types(id, rack_id, ball_id, ball_type)
balls(id, position, in_pocket)
And in rails we could map those using the has_one, has_many, belongs_to in many different ways. In this case, the first method would be unseemly for large families; specifying so many field in the table definition would be a pain. Ultimately we would want to be able to say something like @this_rack.cueball.jumps_table(), but I feel like the sanest database structure is the third, and that would require me to define cueball in the Rack model with an extra sql comparison "FROM ball_types WHERE ball_type='cue'". That seems un-Railsy, because it involves explicitly adding an sql statement. Maybe that's the only way; I don't know, hence the post to this listserve.

Again, to re-cap: the parent could specify each child's identity and type; or the child could specify it's parent and its own type; or a third table could specify the identity of both and the type of the child.

I find myself running into similar patterns often, and using the these different methods interchangeably. Since I'm just now starting to use Rails, I'm trying to figure out if one way will save me grief down the road; maybe based on how Rails optimizes queries internally; maybe based on something I don't even know about.

The major things I deal with when using Postgres is are sequences instead of the mysql autoincrement feature.
This was not an issue for me at all. In the table definitions I just specify "id serial PRIMARY KEY" and Rails handles the rest just fine, as far as I've been able to tell. What kind of problem have you been running into, and how have you dealt with it?

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