|John Ashmead on 16 Dec 2005 12:22:56 -0000|
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.