On Dec 16, 4:08 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote:
> - there's two tables, dataset_table and datasetslice_table.
> - these two tables have *two* foreign key references to each other - an
> inheritance relationship (on unknown columns since they aren't displayed
> here) and another on "parent_id" to "id", which is a one-to-many
> association.
Yes, sorry I was unclear. The inheritance is simple joined-table
inheritance, on columns named id in both tables, the subclass id being
a foreign key referencing the superclass id. There is a discriminator
column "type" in the dataset table with the value 'slice' for
DataSetSlice datasets, and 'base' for the regular ones.
> - for each row in dataset_table, a row in dataset_dimension_association
> may exist.
> - *however*, if a row in dataset_table also references datasetslice_table
> via the inheritance relation, then there may *not* be a row in
> dataset_dimension_association with that dataset_table's id - because the
> mapping says that "dimensions" now needs to reference
> dataset_dimension_association via *another* row in dataset_table which it
> references via parent_id. This is a "constraint" that isn't expressed in
> the schema but is implied by the mapping I see.
Yes --- or rather if there *is* such a row, it is ignored (on the
dataset side, while the backref property on the dimension will still
contain the dataset) because the dataset_table row has type='slice'.
But you're right (of course), this is bad relational design because of
the join inconsistency and the asymmetry in the many-to-many relation.
> The hypothetical issue as far as flush() is that both dimension relations
> would have some interaction when working out dependencies and association
> row values, even for a DataSetSlice object, and would incur conflicting
> data. I'm not sure if that is the current behavior and would have to
> verify, though the warning stays in place since with inheritance, you need
> to look at relations from table hierarchy A to table B in terms of the
> bottommost table in the A hierarchy - relational inheritance is not really
> "polymorphic" in that way, for the "join" reasons I mention above.
Very true. Thanks heaps for putting that warning in the code; I would
have made do with that flawed design if you hadn't.
This was driven by the desire to (a) let slices be datasets in their
own right (fitting them naturally into our system's inventory,
category hierarchy, browsing UI, etc.) and (b) avoid the duplication
of explicitly associating each of the slices with the same set of
dimensions as its parent dataset. So I wanted to override the
dimensions property in the DataSetSlice subclass, delegating to the
parent dataset (the OO way), but that property was injected by the
mapper, so I strayed down the path of overriding it there.
I could consider this dimensions property the “raw dimensions” (maybe
rename it as such) and define a separate (non-ORM) property for the
“dimensions to use”, delegating to self.dimensions in DataSet and to
self.parent.dimensions in DataSetSlice. But then still (a) dimension
associations for a type='slice' dataset do not make sense and should
be constrained not to exist, and (b) querying for datasets having a
given dimension will only find non-slice datasets (though an outer-
join would draw in the slices). So the relational design flaw is still
there.
Instead I will probably just add the duplicate dimension associations,
a lesser evil. Additions/removals of dimensions of existing datasets
will probably not be common anyway. And possibly there will be reason
to let slice dimensions differ from parent dataset dimensions later
on. (A dataset sliced to just one value of a given dimension could be
considered not to have that dimension, for instance.)
Thank you for your characteristically helpful response! (And for all
the work you put into SQLAlchemy in general, and into supporting its
users.)
Regards,
- Gulli
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
