Results 1 to 11 of 11

Thread: Bad design ?

  1. #1
    Join Date
    Mar 2010
    Posts
    2

    Question Bad design ?

    I want to represent many computers that are connected trough ports. Each computer has 5 ports

    Which schema is better ?

    ■ one table : computers

    computers
    id_computer
    name
    port0 (recursive foreign key => id_computer)
    port1 (recursive foreign key => id_computer)
    port2 .
    Port3 .
    Port4 .

    V S

    ■ two tables : computers and connections

    computers
    id_computer
    name

    connections
    computer_init (foreign key => id_computer)
    port_init (values : 0, 1, 2, 3 or 4)
    computer_end (foreign key => id_computer)
    port_end (values : 0, 1, 2, 3 or 4)


    Thanks in advance.
    Last edited by Alejandr0; 03-09-10 at 15:35.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Alejandr0
    Which schema is better ?
    The 2nd design looks better as you don't have repeating groups (port1, port2 etc). Would it be worth having a field for the type of connection in your 2nd design? And what type of network is connected up in this way any way?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    The first design is better if you know (for sure) that you have a fixed number of identical ports on each computer, and if the ports themselves do not have additional attributes that need to be tracked.
    Sounds like a homework assignment to me, though....
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I still want to know what type of networks are connected this way, are things done differently out in Spain?

  5. #5
    Join Date
    Feb 2010
    Posts
    16
    More of a question than an answer, but I wonder what the experienced guys think about the possible performance impact of self-joining for each of the 5 computers, as per the first option... I'm sure in real terms the difference would be neglible, due to the apparent small scale, but I wonder how something like that would scale (e.g. what about when you've got 10x more computers in the system with twice as many ports - twice as many joins, etc.).

    If you go for the second option, think about how you'll enforce the integrity of the relationships you describe... E.g. a primary key based on the computer ID *and* the port number to prevent duplicates and how to model unused ports... Would you insert NULL records (e.g. Computer 1, Port 1, NULL) or treat the absence of any record for a given computer / port to model an unused port?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I posted a reply, but deleted it for now since assuming this is homework it gives more detail than I would like.

    My vote is categorically for option 2. All my reasons are regarding avoiding update anomalies and writing huge, complicated queries to answer even the simplest of questions and none with regard to performance.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    The second option is definitely more robust and more flexible, but also more complex and possibly slightly less efficient. The first option is simpler, but limited.
    The business rule that "All computers have five and only five ports" is what leaves the decision up in the air, and also what makes this smell like a class assignment. Its unrealistic.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I think that as long as we've cleared the issue up for Alejandr0 that's all that matters
    In future he might consider the easier option is to just to copy off one of his fellow students rather than get help from experts off the web.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I often try to limit myself to five and only five ports but I can never resist finishing the bottle.

  10. #10
    Join Date
    Mar 2010
    Posts
    2
    Thank you all!

    Don't worry, this is not a class assignment nor a bizarre network in a Hispanophone country

    I have forgot an attribute for ports. I think i will choose the 2 solution.

    Maybe i will add a third table :

    computers(id_computer, name)
    nodes(id_node, id_computer, node_number, type)
    connections(node_init, node_end)

  11. #11
    Join Date
    Feb 2010
    Posts
    16
    Quote Originally Posted by pootle flump View Post
    I often try to limit myself to five and only five ports but I can never resist finishing the bottle.
    </AdditionalCharsToBypassMinLengthConstraint>

Tags for this Thread

Posting Permissions

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