Cassius Rosenthal on 12 Dec 2005 19:09:38 -0000

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

[PhillyOnRails] Rails and Database Design Issues

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.

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:

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

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

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.

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?
talk mailing list