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.
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?
Search for EAV design model (Magento uses it for extending products) or you can try to work with a NoSQL based solution that has row-modelling support by default (CouchDB, MongoDB, but beware of NoSQL).
In order not to over-inflate the topic, I’ll leave you to googling
lol, is this you suggesting I should just stick with the current method, and make my system create/alter stored procedures based on any alterations I make???
i’m not so sure i would attempt to generate a stored proc from user input data in a control panel application, but if you’ve got the programming chops for it, be my guest
all i’m saying is that if you want to pull anything meaningful out of an EAV database, the queries are horrendously complex (to say nothing about inefficient, which they often are too)
Yup, they are complex and it takes hours to write them, especially to debug them. They are slower to retrieve the data than the regular approach, but you do gain what you want - the ability to add new attributes (rows) without changing procedures and table schemas.
You can, however, give it a shot with NoSQL solutions (which aren’t good for any transactional purpose at this point) but yes, if you don’t like EAV approach and you want to be able to dynamically extend your CMS - there aren’t many options I’m afraid
That’s a shame, I’m going to look into this NoSQL, but failing that I guess i’ll just have to alter database structure as the system is building the modules! I’ve been looking around to any kind of database pattern which would allow this kind of control but I’ve not been able to find anything.
