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.

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?

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

I still want to know what type of networks are connected this way, are things done differently out in Spain?

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?

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.

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.

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.

I often try to limit myself to five and only five ports but I can never resist finishing the bottle.

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)

</AdditionalCharsToBypassMinLengthConstraint>