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

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?

13.10.2009 22:45:32

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 for an excellent example).

13.10.2009 23:30:21
Thanks for the link for friendfeed, a very interesting and informative article. When taking into consideration cost, maintenance, my skills etc I think a document-orientated dbms solution may be a bit tricky to develop and maintain and the client would not have near enough time/money to invest in a solution lilke this. I believe it is the correct answer for this question though.
Niall 14.10.2009 10:58:35

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.

14.10.2009 09:41:06
Thanks for the links. I went through them briefly and will research them properly now. I'd love to be able to use the RDF approach, I'll see how easy it is tom learn/implement.
Niall 14.10.2009 11:05:17

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

14.10.2009 11:03:10