|
[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.
HTH,
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:
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.
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
talk@phillyonrails.org
http://lists.phillyonrails.org/mailman/listinfo/talk
John Ashmead
Ashmead Software & Consulting, Inc.
139 Montrose Avenue
Rosemont, PA 19010-1508
(610) 527 9560 fax (610) 527 4640
john.ashmead@ashmeadsoftware.com
http://www.ashmeadsoftware.com/
_______________________________________________
talk mailing list
talk@phillyonrails.org
http://lists.phillyonrails.org/mailman/listinfo/talk
|
|