Help with Supertype/Subtype? (and categories..)

Question

I have a service table. Each service is defined by 1 main category and 1 sub category.

For example,

Service = Joe's Web Company, MainCategory = Information Technology, SubCategory = Web Development

Each service offered will have a common set of properties (cost, location etc)

Each service will also have a set of attributes specific to the SubCategory.

So in my example above Joe's Web Company may have the following attributes: PHP(BOOL):1, MySql(BOOL):0, Javasctipt(BOOL):1 etc

Or for an actor they may have the following attributes: EyeColour(ENUM): Blue, Height(float): 5.11

So, I am thinking a supertype/subtype relationship will work best however we could be talking in excess of 500 tables.

I also need to be able to search services across Main Category. For this I was thinking of creating a keyword column in the master service table so I don't need to lookup each subtype's table (some categories may have 50 subtypes/tables). I'd run a script every night to populate this column with text explaining the attributes from the subtype for each service (eg for Joe his keyword column would contain 'PHP Javascript').

Does this approach look ok or would an EAV solution fit better considering the number of tables?

Answer 1

I don't think creating 500+ tables is a way to go. It looks to me that your structure is "schema-less" rather than purely relational. I'd consider a dedicated document-oriented dbms (Mongodb, Tokyo Cabinet) or roll out your own, using mysql as low level data storage (look here http://bret.appspot.com/entry/how-friendfeed-uses-mysql for an excellent example).

Answer 2

I think document-oriented dbms is a good suggestion, see CouchDB/PHP or MongoDb/PHP. I'd probably prefer a graph database, where RDF comes to mind. I found two implementations in PHP, haven't tried them myself: Easy RDF and RAP.

Answer 3

A second question of sorts..

If the document-oriented dbms solution turns out to be too tricky to develop/maintain how does this look for an alternative solution?

Having the attribute bundle on the sub category makes my design schema-less. What if I moved the attribute bundle up to the main category level. A lot of the attributes within each sub category will be shared with other sub categories anyway. The drawback to this is a totally unique sub category will impose it's attributes on every other sub category in the main cat however we could limit the use of these attributes on unique sub categories. I could use application logic to determine what attrbites a sub category can use etc..

Basically this alternative approach would use the traditional relational supertype/subtype model however I would have quite wide subtypes.

ps: will vote for your answers when I get enough credits! need two more lol