Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Sep 2007
    Posts
    6

    DB design for dynamic data

    Hi,

    I currently need to design a DB to cater for a dynamic object. I will describe the situation as best as I can.

    I have an application which currently takes in some XML data. The xml data is permitted to run through a filtering process where the business people are allowed to choose what data they want.

    This data is then stored inside an object and the data is to be inserted into 1 or many tables.

    The idea is that the xml data can change dramatically each time but the expectation is to have the tables generic enough to cater for this.

    I have thought about using dynamic tables but there would be a performance issue. What I would like to know is that what is the recommendations for these types of issues if there any.

    Thanks

    Chris

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Interestingly: SQL Server 2005 supports XML as a datatype.
    Never used it, but it may well be worth a look in!
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2007
    Posts
    6
    we would still like to query the database for field type data. Besides we are working with ms sql. Is there any other alternative?

    Thanks

    Chris

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by lasher169
    we would still like to query the database for field type data. Besides we are working with ms sql. Is there any other alternative?

    Thanks

    Chris
    SQL server IS a database server, published by Microsoft, sometimes erroniously referred to as 'ms sql.'

    I would recommend that you avoid trying to implement a "dynamic table." Instead, in the selection process, when the business people choose the data they want, that you create a new xml record which contains the subset of data selected by the business users, and store that xml data.
    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


  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by lasher169
    Hi,
    Hi.
    Quote Originally Posted by lasher169
    I currently need to design a DB to cater for a dynamic object. I will describe the situation as best as I can.
    3...
    Quote Originally Posted by lasher169
    I have an application which currently takes in some XML data. The xml data is permitted to run through a filtering process where the business people are allowed to choose what data they want.
    ...2...
    Quote Originally Posted by lasher169
    This data is then stored inside an object and the data is to be inserted into 1 or many tables.
    ...1...
    Quote Originally Posted by lasher169
    The idea is that the xml data can change dramatically each time but the expectation is to have the tables generic enough to cater for this.
    BOOM!!!

    What you are describing is not a database. I would call it a "data pile". It is going to explode in your face and fling manure across everyone who comes into contact with it.
    Rule of thumb: don't design any database for clients that can't define their requirements.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Sep 2007
    Posts
    6
    hi thanks for the reply. I meant to say mysql not ms sql.

  7. #7
    Join Date
    Sep 2007
    Posts
    6
    thanks for the reply blindman...

    so what you are trying to tell me is that we should have is a fix field table for each new XML that comes in.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    No. Blindman is saying that your users are requesting a coffeepot that doubles as a walk-in freezer. In short, if the users don't know what data is coming in, how can that data relate to anything? Is one user bringing in stock prices for the day, while the next door user is bringing in lottery numbers for the past week?

  9. #9
    Join Date
    Sep 2007
    Posts
    6
    Hi,

    thanks for the quick reply. What will happen is that the user will be presented with a full xml, the user will then select all those fields that they want and its the filtered xml which will be persisted and a schema of this new xml is saved. When other xmls come in the user will be offered a choice to choose from an existing plan or creating a new one. Does that sound more reasonable?

    Thanks

    Chris

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    No, that description looks like they want something that is both a coffee pot and a walk in freezer now, with the ability to become a space shuttle once they get a larger XML file. If your target machine was a HAL-9000, then this goal might be reasonable, but there are some potentially unpleasant side-effects to that too.

    What happens when the XML file contains audio content, or better still video? How will your code adapt to handle that? What if some of the incoming XML contains data governed by HIPPA or SOX and the lawyers come looking to you for failing to provide the appropriate safeguards (and with a client like you've described this is only a matter of time, the question needs to be when will it happen, not will it happen)?

    This project is an instance of Pandora's box. My advice is to run, and if you can't run then re-negotiate now!

    -PatP

  11. #11
    Join Date
    Sep 2007
    Posts
    6
    i would like to thank everyone for their advice and appreciate all the comments. I will be going back to argue now

    Chris

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Yes, Lasher.

    The problem is that the sum total of your business requirements appears to be "Build us a database application that will hold any data and do anything we need it to."
    Obviously, we can't help you with this. Because if by some chance you were to succeed you would put all of us out of business!
    The other thing to keep in mind is that your users seem to be asking for an application unencumbered by any rules or constraints. Sounds great, right? A database that any user can do anything with? Problem is, one of the little understood benefits of a relations database is that they DON'T allow users to do anything they want. A well designed database demands that data follow rules and constraints that ensure that the data that eventually comes back out of it is reliable, consistent, and accurate.
    If it's not practically useful, then it's practically useless.

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

  13. #13
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Quote Originally Posted by blindman
    Because if by some chance you were to succeed you would put all of us out of business!
    might not be such a bad thing. kind of miss working for a living.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  14. #14
    Join Date
    Aug 2007
    Posts
    4

    Dynamic Data Storage - build it yourself, design smartly

    If the Conceptual Model is done correctly, before designing the Logical and Physical Model, you will be able to capture any new data without dedesigning the DB.

    A conceptual model should start with a broad generic Ontology which can generically capture all db entities and relationships.

    For example instead of having companies, customers, players, groups, employees, etc. you could store/classify all these entities as Parties.

    The relationships between these entities (i.e. what u are usually familiar with as Foreign Keys and such) are also manage separately in entities (Link Entities).

    So, when new relationships arise in the data you can dynamically store them by defining a new Association Type (i.e. A person may be a member of a group, an owner, an employee, a customer - all of which can be stored in existing tables (Parties and Party-Associations) as needed).

    If you want to read more about these types of approaches there is a lot out there. Check into Events-based models, Object-Relational, Associative Models, etc.

    Also, there are many standard Ontologies (check out DublinCore) which are being used in Metadata models in many implementations around the world These will help u design the Base Entities and get going.

    Alternatively, you can always looks at XML as a flexible storage methods (although this is not my prefered way because it too is a type of data silo - requiring an intial DB Design).

    Always start with the Conceptual before moving to E-R

    Cheers


  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rdalter, i stumbled on dedesigning, figuring it was just a typo

    but when i got to broad generic Ontology, i knew something was wrong with me

    i've been designing databases professionally for about thirty years and i've never needed to use one of them things, whatever it is

    also, something that puzzles me is why you can use such nice big fifty-cent words and then toss in the occasional "u" for what, populist appeal?

    i think the world of database is slowly passing me by, because i've never even heard of data silos -- are they kept on server farms?

    one thing i have learned, though, is never to over-generalize a design -- that's a slippery slope to EAV, and that way lies madness, as blindman would say

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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