Hi Luca, There are a couple of things you could try:
1) Analyse the business logic further to figure out what fields structurally necessary for different types of product and/or important for queries, decide how far you want to take the inheritance tree from there. 2) Fields that are likely to change you could combine and store in a text field (e.g. in XML) and parse in your python application, some queries are still possible by searching the text (SQL: ..LIKE.. I think). 3) Use a one to many attribute "properties" (or some such) .. I guess that is the tags approach .. with a properties table having a foreign key to the product/service table, a property name and a property value (probably text, so if it would have to represent an e.g. an integer, querying for e.g. > would be a problem). I think it is a well known problem, especially for companies with many different products, like an electronics web shop, where customers want to search for products with specific properties. Having a separate table for each type of product is a pain, especially if new types come out rapidly (tv, lcd, plasma, 3D TV), thats why you often get choices for e.g. screensize instead of being able to set > 36" . I guess they use option 3 or maybe 2. Hope this helps, Lars On Apr 7, 10:36 pm, Luca Lesinigo <l...@lesinigo.it> wrote: > Hello there. I'm using SA-0.7 to develop an application that should > help me manage my company's services. > > A central concept here is the order, it could be a service (like one > year of web hosting) or a physical item (like a pc we sell). So far I > generalized them in two classes: the Order and the ServiceOrder - the > latter simply inherits the former and adds start and end dates. > > Now I need to add all various kinds of metadata to orders, for > example: > - a ServiceOrder for a domain hosting should contain the domain name > - a ServiceOrder for a maintenance service should contain the service > level for that service (say, basic or advanced) > - an Order for a PC we delivered should contain its serial number > - and so on... > > I could easily add child classes, but that would mean to keep and > maintain that code forever even after we stop using it (ie, next year > we stop doing hosting) or when it's not really useful (many things > will just have some 'metadata' in them like a serial number or similar > things). I'd also like to avoid having to add code every time we just > hit something slightly different to manage, when we just have some > additional data to keep track of. > I wonder what could be an intelligent approach to such a situation. > > One idea I got could be to add an 'OrderTags' table / class that would > associate (tag, value) tuples to my orders, and somehow access them > like a dictionary (eg. Order.tags['serialnumber'] = 'foo' or > ServiceOrder.tags['domainname'] = 'example.com'). But that will > probably keep them out of standard SA queries? For example, if I want > to retrieve the full history of a domain we're hosting, how could I > query for all orders with (tags['domainname'] == something)? > > I'm looking for advice on how to structure this data, and how to best > implement it with python and sqlalchemy-0.7. > > Thank you, > Luca -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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.
