Great, thanks for replying. His "generic schema" approach is essentially what I'm already doing for other more complex parts of my model. http://www.agiledata.org/essays/mappingObjects.html#MapToGenericStructure
On just this Companies-Vendors-Manufacturers part, I was hoping to get away with something simpler. So the spirit of my question was "help me make sure I'm not overlooking a simple easy way that SA's joined inheritance already handles what I want." I just didn't want to miss anything. As it stands, I guess I'll just have to wire up whatever I need and see how it goes. ----- There ends my question, the following is just shared thought: ----- I'm pretty excited about the generic schema approach, of which the link above describes the basic essentials. For better or for worse, I reinvented it from scratch, being unaware of most related/relevant literature when I first was working on this stuff in 2008. SqlAlchemy has made it amazingly easy to implement. I have Products that come in various Subproducts (for instance a model of shoe is the abstract Product, but you can't buy the product itself; you can buy it as a subproduct: e.g., Size 10 of that shoe, in a specific color). Each product references one or more product types (here's one of the points where there is multiple inheritance going on). The product type does not carry information itself, it's a key to one or more attribute groups. When creating a Product, I specify one or more product types, which thereby pulls in one or more attribute groups, ultimately generating a resultant set of attributes that the product expects to have. In the basic view of this, the attributes are not given values at the Product level. The creation of a Subproduct is where all of the product's attributes are associated with specific scalar values. This is all done through a type system where I can create collection-based or numeric (or other) types and their associated validators on the fly (during runtime) and connect them to attributes. In a more advanced view, necessary in order to really model actual commercial product domains, it is necessary for some attributes to be assigned values at the Product level. The attributes themselves carry flags that declare whether they are to be assigned values at the eventual subproduct instantiation (the default), or be held and assigned at the product level instead. Then to further match the model to what's really going on in the domain being modeled, it happens that we need to be able to create a product type structure that works for 90-95%ish of the products that will use it, and the remaining 5-10% will simply suppress or override some of the attributes. An attribute can be suppressed altogether, or the default declaration of whether that attribute should be assigned a value at the product vs. the subproduct level can be overridden on a per-product basis. Additionally, product types are allowed to multiply inherit from each other, thus we get a directed (and I do some processing to make sure it stays acyclic) graph of product type inheritance. Beginning from just a single identifier of a product or subproduct, determining exactly which attribute-value pairs (facts, essentially) are attached to it requires looking all the way up the product type inheritance chain/graph. This would be a lot more painful without SqlAlchemy and i am endlessly thankful for all of the loading and associating machinery we're so lucky to have here. This all could begin to sound terribly inefficient, and yes, I'm not planning to scale it to too many millions of products! But I'm optimistic that with appropriate view creation and good indexing and caching I can keep it reasonably performant. In fact, once a product or subproduct is created in this system, I'm interested in creating some sort of frozen view of that item that is able to be loaded extra quickly without too much join activity. That sounds appropriate because, for example, a product will not change its weight often (in fact only when incorrect data is corrected later, or something). Attributes like price will change more often, but I can afford to have writes rather slow as long as reads are quick, so I think that might not be too hard to handle. This is the foundation of a future inventory and point-of-sale application which aims to provide a lot of improvements over currently available software by starting with far better modeling of the actual product data. Most systems out there in production just use a big spreadsheet-like database with lots of irrelevant columns, and columns that have a different meaning depending on what type of row you're looking at, and many of these systems don't even have a system to deal with that, they just expect you to be ok with thinking "Attr1 is color if I'm looking up an article of one type, but it's size if I'm looking up another, or it might hold something else entirely." There's a zope-based alternative that is the closest I've found thus far to doing what I'm trying to do, but they went down the road of delving into Python class mechanics to force all of this to happen via Python classes and instances, rather than just keeping it all relational. I'm trying to build this with a relational perspective through and through. So far the only drawback is the complexity of assembling views of products and items, and of comparing disparate kinds of them; but I actually look forward to that as I believe a number of unforeseen benefits are going to accrue from simply modeling powerfully yet flexibly from the beginning. Cheers, Eric On Nov 21, 3:38 pm, Michael Bayer <mike...@zzzcomputing.com> wrote: > On Nov 21, 2010, at 4:41 PM, Eric Ongerth wrote: > > > I must be in a "search blind spot", I'm having trouble finding > > references and examples for this pattern, though I feel certain I've > > seen examples around the sqlalchemy literature a couple of times > > before. > > > I have a table of Companies, and then further tables of Manufacturer > > and Vendor info which apply to some companies. So a company can be > > stored just as a company, or as a company that happens to be a > > manufacturer, and/or happens to be a vendor. I don't want the > > manufacturer and vendor designations to be exclusive of each other; it > > should be possible for a company to be both. > > The thing I pointed someone to the other day regarding multiple inheritance > in general is > athttp://www.agiledata.org/essays/mappingObjects.html#MappingMultipleIn.... > My general attitude about that example is "yeah great, go nuts !". i.e. if > someone wants to get into it, great, good luck, but I haven't personally ever > had any situation that called for something that complex and if I did, I'd > probably try to get around it somehow. Maybe someday I'll actually need the > feature though, then I'll figure out how to do it generically, then it will > be the next big SQLA feature everyone needs, who knows. > > > > > I thought of just having a boolean field on the Company table for each > > 'child' type. So (company.manufacturer == True) would tell me it's > > safe to write to or read from attributes whose storage is in the > > manufacturer info table. Likewise, (company.vendor == True) would > > indicate that it's safe to write to or read from attributes whose > > storage is in the vendor info table. And of course some companies > > will be filed as neither, so I won't attempt to access mfr or vendor > > info about them. > > so I think if you are trying to get multiple tables to compose into a single > type, its likely very possible using a combination of relationship() and > association_proxy(). relationship() to maintain the linkages to other > tables, association_proxy or something similar (perhaps like the hybrid > example, after all we are just using Python attribute tricks) to create a > one-level-of-names type of facade. > > > > > Regular SA joined-table inheritance doesn't work this way since it > > requires a discriminator column taking a single value at a time. > > Still I would love to use joined-table inheritance if I could. Is > > there a way? > > joined inheritance might be involved, but in a practical sense you have to > think about what your SELECT queries are going to look like if locating a > record requires spanning across three or four tables. -- 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.
