Cassius Rosenthal on 15 Dec 2005 03:27:41 -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, John.  I commented below:

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.
The 'places' table is not redundant because the order of the points is no longer held in the 'triangles' table. In the 3rd method, 'triangles' only has an 'id' field. The "has one :first_place" is the Rails mapping method which would perform the query, look against 'places', and figure out which point to return. It's one more table, but perhaps it helps future-proof the design with flexibility; for example, perhaps multiple triangles point to the same point, but have different placement for them and vice versa simultaneously. Neither 'triangles' nor 'points' are emphasized here, but the relationship between them is. This method is how I would go in postgresql, but again, it seems un-Railsy.

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.
How so? Switching between Ruby and PL seems to be the hard part for me. Could you give an example?

A "places" view could be used in place of the places table, avoiding the redundancy of actually instantiating that table, but keeping the convenience.
I see what you mean. My hunch is that Rails handles views exactly like tables, but I'll have to try it out. I'm not sure the 'places' table is a convenience in Rails. It would be in postgresql, though.

Even if Ruby dislikes stored procedures (sorry to hear that!),
It's not that Ruby dislikes stored procedures. Rails can still call stored procedures, but as far as I know it would necessitate sql statements everywhere in the model, which would invalidate all of the work that went into the Rails object/relation mapping. That's part of the big problem for postgresql, or any advanced database usage. Am I wrong on this?

...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.
Under the hood would be great . . . if the controller and viewer could still operate; but essentially you're taking the fuel and putting it into a completely separate car. Rails will still need to model the data in order to pass it to the controller. Since we *must* have model logic in Rails, and it's not necessary to have model logic is postgresql, I came to the conclusion that it's better to have the logic in one place rather than two. For now at least. Of course I'd rather have it in postgresql, but that seems like significantly more work. Hence the design uncertainty.

talk mailing list