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?
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).