John Ashmead on 13 Dec 2005 13:39:33 -0000

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

Re: [PhillyOnRails] Rails and Database Design Issues

The first two designs seem logically correct. Which is best depends on what use you intend. If you are placing most emphasis on triangles, then 1 is best; on points, then 2.

In the third design, the places table appears redundant: since you know what the 1st, 2nd, & 3rd points are from the triangles table, places can be recreated from the triangles table.

I would prefer myself to start from the SQL & define inside Postgres. With foreign keys, it is easy to enforce the correct rules. Making all the fields not null would make sense here (as it usually does). This would give you the ability to switch between Ruby & PL as required. A "places" view could be used in place of the places table, avoiding the redundancy of actually instantiating that table, but keeping the convenience.

Even if Ruby dislikes stored procedures (sorry to hear that!), much of the benefits of stored procedures can be achieved by using triggers that call stored procedures. In this case, Ruby wouldn't even know about the PL. It would be all under the hood.


John (Database Guy) Ashmead

On Dec 12, 2005, at 2:09 PM, 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.

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

John Ashmead Ashmead Software & Consulting, Inc. 139 Montrose Avenue Rosemont, PA 19010-1508 (610) 527 9560 fax (610) 527 4640

_______________________________________________ talk mailing list