Object and Object meta schema design

Am working on a project that will save many types of objects into the database. These include: articles, polls, writers .. etc. Some of those are unknown to me at design time so what i am trying to do is build a generic table schema that will enable me to save any type of item into this database.

My design

Table Object

objectID int
title varchar
body text
type int #will represent the ID of the type of object am inserting

Having tha开发者_如何学运维t circumstances or requirements might need additional data, i decided that this will go to what i call "object metadata" as follows:

Table Object_MetaData

metaID int
metaKey varchar
metaIntKey int #representing an integer value of metaKey which is calculated using some algorithm to speed up queries
metaValue varchar(1000)

I am doing all the work in PHP and MySQL btw.

A couple of things came up to my mind when doing this design regarding the performance of the database on the long run:

1) Is saving int, boolean, small text, big text into the metaValue effecient?

2) on the long run the metadata table is going to grow quite quickly. Is this a maintainance nightmare? how is MySQL going to to handle this?

3) IN php when fetching objects, i will have to loop each object to fetch its metadata, or load all metadata once using lazy loading or load any single metakey once its requested via __get magic method. So when fetching a list of say 50 objects, at least 50 select statements will be executed when using any of the methods i suggested. Is there a better more efficient way of doing this?

Any thoughts or comments on this design are welcome.

I'd suggest re-architecting your solution. This pattern is called "Entity-Attribute-Value" (EAV), and is commonly seen as an anti-pattern. Instead, define the metadata attributes in columns (of the same table, or another if needed). Use the proper data types as well.

I'd suggest giving Bill Karwin's book SQL AntiPatterns a read. It has some great insights that will give you a hand here specifically. You can get a feel for some of it from this presentation...