Re: DBIx::Class with two kinds of otherwise identical table types

[Marshall]

I haven't used DBIx:Class. But it appears that you have a clear set of Towns and a clear set of Residents. This sounds to me like a "has a" relationship, not inheritance ("is a"). A town has a resident, a resident can only belong to one town. A resident is not a town, ie resident is not a sub-class of town. The White House and airplanes both have wings, but they are not sub-classes of Wing.

I'm not a relational DB guy by any means, but the each resident in the resident table would have a "pointer" to the town that this resident belongs to - there are various DB words to describe this, but that is what it is.

I got lost with this part: tables of the Resident kind (Resident_00, _01, etc), to accommodate a few huge towns. There will be just 2 tables as you described. One that describes all the towns. One that describes all the residents. Each resident has a pointer to a town at a minimum. It is also possible for performance reasons to have the DB update a list of pointers to residents for each town.

The DB can generate tables like "give me all residents in town X". It is also possible to "flatten the DB", by putting all the data into a single table. I mean like the Town table could have stuff in it like Latitude, Longitude, #of restaurants, etc. The resident table just has a pointer to that info. You can generate a table with all the info. That is sometimes done for read performance reasons. But say if the #of restaurants changes, I've got a problem as lots of fields have to be updated.

I guess in short, a Class representation may not be what you want? A few huge towns would normally mean to me that the resident table has a lot of duplicate pointers to those huge towns. roboticus knows a lot about such situations and I will defer as to partioning, etc.

Update: I am working on an SQL project now and I recommend:
Learning SQL by Alan Beaulieu
the concepts map very directly into the Perl DBI.

[dwm042]

Marshall,

My first job as a professional in IT involved writing C++ wrappers for embedded SQL, so handling SQL statements isn't an issue for me. I'm certain I can craft decent enough SQL to do the job.

But like most Perl folks, I'm interested in the new technology and so am designing a Catalyst app in my mind as I work through the Catalyst tutorial (recommended, btw, as a great intro to TT, DBIx, Moose, and Catalyst).

So the question, in abstract terms, is at what point do you need to abandon the two table representation of data and go to a partitioned table implementation? Understand, from my POV, anyone who has ever thought of a file system layout understands where a partitioned table set is headed. You have a table with a column of pointers. The pointers point to the table to use. You would only do this when performance of a single table representation becomes an issue.

I've had and maintained databases with a single huge table. It's no fun when your data load takes several hours and the database is flaky. So I'm thinking about ways around that.

So, knowing whether DBIx supports partitioning is useful to know. Knowing that the TypePad people at Six Apart have run into this issue, and created Data::ObjectDriver to specifically deal with partitioning issues is useful to know too, when considering ORMs.

David.

[roboticus]

dwm042:

Just a couple minor clarifications on partitioned tables:

First, as far as the application is concerned, there's a single table. While it *could* access the subordinate tables individually, it normally wouldn't. When you submit your query, the database server has the task of converting your query against the main table into queries against the subordinate tables: so your application doesn't get more complicated--only the database management does.

Secondly, MS SQL Server doesn't keep a table of pointers to the other tables: Instead there's a function that returns the table. I doubt that other database servers use a table of pointers, either, as that would be another table and index to maintain.

Another advantage of partitioned tables is that a single query on your table can break into a query per subordinate table, and those can be queried in parallel. So many queries are faster that would occur in a non-partitioned table.

...roboticus

[dwm042]

roboticus,

Clarification much appreciated! I know I'm otherwise thinking in terms of analogies to files in file systems, with blocks containing pointers to blocks.

David.