Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2008
    Location
    Suwanee, GA
    Posts
    3

    Unanswered: Design Best Practices Question

    Hi all, I have a question that has been bother me for a while now and was wondering if you all could guide me to some of the best practices for dealing with situations like this one.

    The issue is storing information for the same type of object, but the different types of objects require quite different types of information.

    Example: I want to make a remote connection. I can either make a UDP/IP connection or a Serial connection to the remote device. Now, I want to store this information in a way that makes sense. Currently it is stored something like:

    ConnectionID
    ConnectionName
    ConnectionType //In our case Serial or UDP/IP
    ConnectionParameter1
    ConnectionParameter2
    ConnectionParameter3
    ConnectionParameter4
    ConnectionParameter5
    ConnectionParameter6
    ConnectionParameter7

    Where for the UDP/IP I would give it the IPAddress and Port Number in Parameters 1 and 2, where for the Serial I would give it Com Port, Baud Rate, and all the other good serial information as Parameters 1, 2, etc...

    What are some other, hopefully better, alternatives for making these sort of tables?

    Thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    it really kind of depends on what the system is being used for but you may want to read about Normalization and 3rd Normal form if this is a data entry system.

    Without knowing more about your data model, I am hesitant to offer much advice
    “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.

  3. #3
    Join Date
    Nov 2008
    Location
    Suwanee, GA
    Posts
    3
    Quote Originally Posted by Thrasymachus
    it really kind of depends on what the system is being used for but you may want to read about Normalization and 3rd Normal form if this is a data entry system.

    Without knowing more about your data model, I am hesitant to offer much advice
    Thanks for the response. I understand about Normalization and 3NF, I just can't figure out the proper application to my situation. The specific example problem is one I am working on at the moment (minus some other parameters, but I listed what I did for simplification's sake), but I figured there had to be a class of problems that are similar in nature to mine, that I thought there would be accepted methods of dealing with. Some sort of database design pattern if you will.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    yes there is and it is called normalization. you have a denormalized design and the accepted solution is to normalize it and if you truly understood 3NF you would be able to do that. I do not know how your data is being used or the details about what you are modeling, so I am not going to give you a table schema.
    Last edited by Thrasymachus; 11-30-08 at 20:07.
    “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.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are two possible solutions to this class of problem.

    Normalization leads toward simple, scalable solutions. Normalization requires more discipline, but it can scale up to any arbitrary level of usage. You can mathematically prove that normalization completely and correctly solves a given problem.

    There is another solution that has several variations on the same theme, often known as XML for a storage medium that easily represents unstructured data or EAV for a speudo-modeling technique. This is conceptually much simpler, and for very small problems (up to hundreds of cases) often performs well enough that the simplicity of the concept is tempting. There is no way to mathematically prove anything about this model, understanding depends on intuition and faith.

    -PatP

  6. #6
    Join Date
    Nov 2008
    Location
    Suwanee, GA
    Posts
    3
    Quote Originally Posted by Pat Phelan
    There are two possible solutions to this class of problem.

    Normalization leads toward simple, scalable solutions. Normalization requires more discipline, but it can scale up to any arbitrary level of usage. You can mathematically prove that normalization completely and correctly solves a given problem.

    There is another solution that has several variations on the same theme, often known as XML for a storage medium that easily represents unstructured data or EAV for a speudo-modeling technique. This is conceptually much simpler, and for very small problems (up to hundreds of cases) often performs well enough that the simplicity of the concept is tempting. There is no way to mathematically prove anything about this model, understanding depends on intuition and faith.

    -PatP

    Thanks Pat, that is the sort of answer I was looking for. I can normalize the database over quite a few tables, but it will be a bit of a pain, I'd have to do the full design to see what the end result would look like. I hadn't considered using XML as part of the DB storage medium. I think I can see how it would be both simpler to design for but possibly more complex to code against / use. Thanks very much.

    If anyone else has any insight, I'd appreciate it, but I have a new direction to research and compare against the current implementation and a fully normalized implementation. Thanks!

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Go compare, have a good time. We'll be here to help pick up the pieces after you decide to try XML. It always looks good (sometimes even gets past QA)until you actually put it into production. I know of two successful implementations (out of thousands), and at least sixty that have actually caused small businesses or departments to fail.

    -PatP

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This might be overkill (can't be sure without knowing more about your exact requirements) but another option is to use a supertype - subtype design. Rudy posted a super link recently to a ppt on these....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    why does your link go through google.co.uk?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Give ya three guesses.....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    guess #1: because your computer is fubar
    guess #1: because you are fubar
    guess #3: because i am fubar

    http://www.cbe.wwu.edu/misclasses/mi...persubtype.ppt

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

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Possibly #2...
    I googlated it and then right clicked the link and selected "copy link location". I did not realise it included google in the address....
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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