Design of an 'EAV' or 'Class/Concrete Table Inheritance' database for stock control

Question

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.

Answer 1

• 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.

Answer 2

Take a look at this discussion.