SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot yukimushu's Avatar
    Join Date
    Feb 2005
    Location
    United Kingdom
    Posts
    183
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Dynamic database design

    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?

  2. #2
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by yukimushu View Post
    I also hate the idea of constantly editing/altering table structure too.
    you're gonna hate writing queries against an EAV schema far, far more than that

    trust me on this

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot yukimushu's Avatar
    Join Date
    Feb 2005
    Location
    United Kingdom
    Posts
    183
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you're gonna hate writing queries against an EAV schema far, far more than that

    trust me on this

    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???

  5. #5
    SitePoint Zealot yukimushu's Avatar
    Join Date
    Feb 2005
    Location
    United Kingdom
    Posts
    183
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by furicane View Post
    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
    Thanks a lot for the heads up, I'll check them out!

    Quote Originally Posted by yukimushu View Post
    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???

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by yukimushu View Post
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    SitePoint Zealot yukimushu's Avatar
    Join Date
    Feb 2005
    Location
    United Kingdom
    Posts
    183
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by furicane View Post
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •