Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Mar 2007
    Posts
    3

    Question Extensible Database Design

    Hi,

    Let me start with a scenario. Let's say I have a contact management system. The users will be able to add their customers information into the system. If they need more fields, they will be able to add it via the application (for example; let's say second business phone or fax number). Technically I'm looking for user defined fields. What is the best way to handle this in database design? The database should be flexible and scalable.

    I've seen this kind of functionality in CRM type software. Any idea?

    Thanks,
    semaj

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    This is a horrible way to design a database, and is indicative of poor requirements gathering.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2007
    Posts
    3
    Why "poor requirements gathering"? Could you please tell me what you meant?

    Thanks,
    semaj

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I mean that rather than taking the time to figure out what your clients need, you are trying to throw in functionality to let them define it themselves on the fly. That is a recipe for a disorganized, buggy, and unreliable database design.
    Software companies like to advertise this sort of customizability as a feature, but in reality it is a license to let the client screw things up as many ways as they desire. I have worked with so many hosed up databases like this that it is ceased being funny.
    Why not take it to its logical conclustion? Give the client a completely customizable application that they can configure any way they want? In other words, nothing but a CREATE DATABASE statement. That should make them all happy.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Mar 2007
    Posts
    3
    That is the customer need rather than my functionality. I just want to get some idea how an application can have such a user defined fields (not all, but some udf).

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    And again, if the customer NEEDS (not requirements) are well defined, there is no requirement for unneccessary "spare" fields.

    anything that you put in place is going to be a kludge. If you try spare text fields, you will also HAVE to add a "shadow" table to hold the table name, field name, field "type", field input mask, limits, etc. Then, you'll have to enforce data conversion, validation, etc, either in convoluted triggers, or in self-modifying constraints, which would be a disaster waiting to happen.

    I agre with blindman here - research the pros (none in a well defined system) and cons (way more expensive, both up front, and in maintenence, way more fragile, way more complex,) and show the customer why this approach is a bad idea, and one that will cost them MUCH more than they would ever want to pay, in dollars and downtime.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by semaj2007
    Let me start with a scenario. Let's say I have a contact management system. The users will be able to add their customers information into the system. If they need more fields, they will be able to add it via the application (for example; let's say second business phone or fax number). Technically I'm looking for user defined fields. What is the best way to handle this in database design?
    There simply is no good way to handle this. Having application code make modifications to table schemas at runtime is undoubtably the WORST method. If you absolutely have to do this then throw a couple of spare undefined columns onto your table to start with. And make sure you write a support contract that favors you, because you will be getting lots of calls.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm not in support of the approach either but.... if you do go down this like then I would throw in a a few spare columns of each data type group (say varchar, int, datetime) to get at least a modicum of data constraint.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Mar 2007
    Posts
    5
    I too would also like users on the web to be able to add their own fields.

    I believe that this is a real requirement. Simply dismissing this as poor requirements gathering is silly when you have no idea of what the requirements are.

    From what I have understood so far, it seems a challenging problem with no decent technical solution. The standard solution seems to be creating multiple columns for different types. Obviously this sucks since its not a normalized DB structure.

    Any more thoughts on this? I know MS Sharepoint supports dynamic fields. I wonder how they do it?

    Is this a constraint of DBMS in general?

  10. #10
    Join Date
    Mar 2007
    Posts
    5
    I was interested to find out Microsoft's solution to store custom user fields, since you would expect them to use the best possible solution.

    After a few google queries on MS's sharepoint database schema, I found this page:
    http://msdn2.microsoft.com/en-us/library/ms998711.aspx

    Basically a UserData table with 201 columns!!!! hehehe... how is that for database design!?

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by andymagic1
    I too would also like users on the web to be able to add their own fields.

    I believe that this is a real requirement. Simply dismissing this as poor requirements gathering is silly when you have no idea of what the requirements are.

    From what I have understood so far, it seems a challenging problem with no decent technical solution.
    I would like an airplane where passengers could add additional seats, wings, flaps, and cargo space during the flight!
    Please don't dismiss this idea as silly, because it would be really really cool.
    From what I understand, it seems a challenging problem with no decent technical solution.

    Quote Originally Posted by andymagic1
    I was interested to find out Microsoft's solution to store custom user fields, since you would expect them to use the best possible solution.
    Ah, to be young and naive once more....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Mar 2007
    Posts
    5
    Since this thread couldn't provide any intelligent discussion on this topic, I went and did a little more research on Google.

    Some of you may find this useful.

    Blogs discussing different techniques.
    How do you extend and customize a database?
    Database Schema to Support Customizable/Extensible Application

    I found this to be the best article that provides pros and cons for extensibility patterns.
    Multi-Tenant Data Architecture

    I've still not decided on a database design yet, but I want to take my time to find a good balance between scalability and flexibility for users.


    In the case of a contact management system, I too would agree that an extensible design is a bad idea. It is clear what sort of data users will want to store, so its just a case of identifying those requirements. My system is a little different, since it is not possible to guess at what sort of data might be stored.
    Last edited by andymagic1; 04-11-07 at 12:40.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Thanks for checking back and posting the articles.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by blindman
    I would like an airplane where passengers could add additional seats, wings, flaps, and cargo space during the flight!
    Please don't dismiss this idea as silly, because it would be really really cool.
    From what I understand, it seems a challenging problem with no decent technical solution.

    Ah, to be young and naive once more....
    I'm with you Blindman!

  15. #15
    Join Date
    Jul 2009
    Posts
    1
    Dear andymagic1:

    thanks for researching the topic and providing the links, this is indeed very useful.

    I am currently facing a requirement that is forcing me to go down this path of a flexible schema, although it is certainly not my preference.

    It has been a couple of years since your last post, and I would like to know what kind of experiences you have had with this approach. Did you ever go into production with such a flexible schema? What would you do differently now? Any particular pain points that you can highlight? Recommendations in general?

    Thank you in advance.

Posting Permissions

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