Hi people, quite a weird one to explain here so I appologise if I've not explained myself very well.
I'm currently in the process of making a content management system which allows me to create modules/databases through an admin front end, such as a "Pages" module or a "Products" module, to which a form is dynamically built in the users' admin area based on those aforementioned fields.
Contemplating the database design, I need the ability to add new database fields to the modules. Say for example, I wish to add a new field to the products table called "old_price", I would need to create a new float field suitable to store a price.
This kind of adapability wouldn't allow room for stored prodecures as I would constantly be adding and removing columns via the content management system, throwing the stored procedures out of whack, it would also cause havoc having to reindex large tables if i added a new column. I also hate the idea of constantly editing/altering table structure too.
Adding a new field would simply add a new column on the end in a linear manner, and if i were to have products which had hundreds of fields, the database table would then have hundreds of columns.
Products Table Columns:
prod_id(int) | prod_title(varchar) | price(float) | old_price(float)
One way around this I thought of was making the content manangement system create/edit those stored procedures based on the new fields/columns in the database.
Can any of you guys think of any kind of database design which would allow me this kind of flexibility without having to dynamically create/edit stored procedures?