Object Relational Mapping with special requirements (select basis + all child objects, ...)

Question

I have the following PHP class structure:

I want to save the objects in a database (MySQL, MSSQL, postgreSQL and all standard providers offer).

My requirements are:

1. list of all electric_devices / all computers / notebooks ... including the objects of the subclasses with all attributes belonging to the object with filter and order possibility (filters for all attributes)
2. select a single record via ID
3. insert new objects in the DB structure (reading is much more important than writing)
4. not too many joins and as little database requests as possible

I found some possibilities, but there is no one, that matches all my requirements:

1. Class Table Inheritance: it is very complicated to get all items including the subclasses and I couldn't find a automatic way (so that I have not to define for each subclass a separate join statement) for generating the correct joins. Another problem: If you want to find an object by ID you don't know which join path you have to use.
2. Single Table Inheritance: too many distinct attributes --> NULL fields
3. Concrete Table Inheritance: for searching all items you have to do many database requests, since UNION requires same number of columns
4. EAV: if filter is needed 1 join / attribute --> performance would be very bad (is there a possibility for just joining the filter attributes and the rest as rows?)
5. NoSQL database: no provider (server is too expensive and I don't want to care about administration)
6. postgreSQL built-in inheritance: is not working the way I need (from the basis you can list all electric_devices, but just the common attributes)

Information:

• the total number of stored objects will be about 100
• the complete number of attributes (~100) will change seldom
• many of the attributes have predefined multiple choice values
• each object has between 20 and 50 possible attributes

Finally for me there is no way, but I think there must be a possibility, other portals do it the same way (for example eBay or any small ad portal), probably I have thought about it so much, that I don't see the easiest way.

Is there an approach, that fullfills all requirements?

Thanks for every help (links, keywords, ...) , if something is not clear, just ask.

Answer 1

as you listed no-sql for your research: I do think that would be a very easy solution for you, say mongodb. Your requirements are met for this case and there are neat solutions for object-mappings at hand, Doctrine ODM for example. Also, hosting is definitly available for it, http://dotcloud.com for example. Though I don`t know what budget is availabe to you. With this provider you will start at appr. 8$ a month.

Answer 2

You may not have looked at Class Table Inheritance closely enough. In particular, you should look at how subclass tables get their ID field filled in. It isn't an autonumber. It's a copy of the ID field from the corresponding superclass. This is "poor man's inheritance". It's a little complicated to program the insertion of new items, but the joins you will need are slick, easy, and fast. All you have to do is use the ID of the subclass and the ID of the superclass in the join condition and you're done.

And you can create 5 views to join generalized attributes and specialized attributes for your case.