John Ashmead on 16 Dec 2005 12:22:56 -0000

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

Re: [PhillyOnRails] Rails and Database Design Issues

Dear Cassius,

Thanks for your comments.

I hadn't realized that "first_place" in the triangles was a method not a field. Thanks for the explanation. In that case, as you say, the places table is essential.

It can be useful to be able to go at the same data in multiple ways. Batch reports can be easier going at the database with say Crystal reports, some kinds of updates are easier from stored procedures, and so on. This is one advantage of centralizing access to data in a database: databases intrinsically tend to be less tool-centric.

I've had good results using frontend tools (Microsoft's web tool, Visual Basic, HTML) for GUI parts but then putting all database access in stored procedures. This has made everything easy to debug: either the stored procedure has been given the right arguments but is doing the wrong thing with them or it has been called with incorrect arguments. It usually hasn't taken more than a few minutes to see which developer gets the black spot: Mr. Gui or Mr. Stored Procedure! Since bug location is a major time sink, this is a big win.

I like triggers for accesses that are in some sense part of the data. The classic example is keeping a total on an invoice table (a great convenience) but using triggers to make sure that no matter how you add, change, or delete the invoice items, the invoice total is correct (once you've gotten the triggers right! :)). In general, triggers are a godsend for keeping instantiated, derived data (like totals) correct. For other things, it depends.

The deep problem, as I understand your comments, is doing a clean map between the object model and the relational model. This is an old problem but still not solved. I'd tend to keep long term (i.e. lasts across sessions) data in the database but manage all per session data in the frontend. But that is a personal preference.

The scale/duration of the problem is another issue. For short term/ small projects keeping everything in one framework makes sense. Longer term/larger it makes sense to break things out into more specialized tools, i.e. web in Ruby, data in postgres, data analysis in Excel and so on.



On Dec 14, 2005, at 10:27 PM, Cassius Rosenthal wrote:

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

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

_______________________________________________ talk mailing list