I am developing a stock control system for a construction project. The storeman is responsible for adding new stock and distributing/returning it to/from employees. The items (and hence their attributes) will be very varied; e.g. steelwork, clothing, plant/machinery, tools, etc.
My question is whether to go for a Class/Concrete Table Inheritance or EAV based schema. I don't know what attributes items will have, so presumably a Class/Concrete Table Inheritance approach would require the end user to add new tables to the database (is this possible?).
Please see my proposed EAV schema. Would this be a reasonable solution? I think I'm right in saying that to identify an item of stock, it would be necessary to perform multiple self joins in a query on the 'EV' table?
N.B. Developing using PHP, MySQL and Zend Framework.
If attribute changes are few and far between, go for Table Inheritance, but the changes to the schema should be done by yourself or a DBA. Programmatically modifying your schema based on user input seems like a bad idea.
If attribute changes are fairly common, consider using a document-oriented database like MongoDB or CouchDB.
If attribute changes are common and you are restricted to relational databases, go with EAV.
