Hi all,

I have a db design dilema and since I'm a novice in such area I think I would ask for a little help.

I need to represent Network Elements having different ports which will then be connected together.

To make it easier to get the idea, I'm talking of routers and switches which have many ports each which will then be connected together. Of course, a physical port can't be physicaly connected twice so this is also a requirement.

I have included a picture of what I've came up with which is of course incomplete since I wouldn't ask your advise

Robert

Since a port can only be connected once and only to another port you can drop the third table and just add a column to the port table which contains a port id that it is connected to (joining the table to itself). When you make the connection your program (or a trigger) must make sure that both port rows must have null connection columns and both must point back to different network elements, then you update both rows to contain the other rows key. When you drop a connection simply make both columns null again.

I'd create a connection table with two columns, from_port and to_port. Create a unique constraint on from_port, a unique constraint on to_port, and a check constraint to make sure that from_port does not equal to_port (unless you have to allow for loopback plugs). This uses constraints to cover everything except for two different connector rows with the from_port and to_port reversed (which effectively describes the same connection).

-PatP

MarkATrombley, your idea seems quite easy to implement/validate for this requirement.

I kinda also had an other requirement that popped into my mind after I read your excellent proposition. Sorry if I forgot to add it to my first post.

There can also be LAG or etherchannel interfaces. For the one of you who have no idea what it is, it is a logical interface that bundles 1 to x number of physical interfaces together on the same network element to improve bandwidth or redundancy. As you may have noticed, I included "1" as a possible number of bundled interface. This is because we often deploy a single physical interface in a LAG configuration. This setup offers no advantage over a regular physical interface in terms of bandwidth and redundancy but it will facilitate the future addition of other planned physical interfaces in the bundle when required.

Now, a physical interface can only be connected once to an other physical interface of an other NE. After that, we can bundle 1 or more physical interfaces together into a logical LAG interface. After that, LAG interfaces of 2 different NE are also connected together.

That said, The LAG to LAG interfaces connections can be deducted from the physical connections because 2 physical interfaces that are part of a LAG group can only be on the same NE. The interfaces bundled in the LAG group can't usually be splitted between different NE. But, I do have the requirement to be able to connect it to different NE's.

I know some of you that are not too experienced with networking may find this setup a bit weird but it is definitely possible for special purpose.

I'll try to show here what I mean :


Using your idea of an other column it solves the physical connection issue. I would then add an other column to save the related LAG group ID which is only a number.

Parsing this with SQL combined to Programming code would be quite easy I think.

Your feedback on my logic would be appreciated.



Pat Phelan, Your proposition seems to be a little hard for the beginner in me to understand. Please don't be vexed because I am just limited by my knowledge so it has nothing to do with your idea. That said, I would still like to try to understand your idea that may change a bit with what I added in this post. If you could provide a small diagram (but feel free to do it or not) it would make my brain switch to the next level.

The reason I still want to dig into your idea too even if the one I've represented here seems to make sense to me is that I realized recently, and I must say that I'm still at the bottom of the learning curve, that good DB design + good SQL queries saves you hundreds, no tons, no millions !!! of codding lines in your applications.

So I will probably pick the best option that I can understand which will saves me time in coding according to my knowledge level.

Thanks to both of you for your precious help.

Robert.

I think you ought to spend more time developing your model before trying to commit it to a SQL schema. You also haven't made it clear what you're trying to *do* with this schema.

IIRC, the OSI model has seven layers and is arguably not complete. I'm not saying you should be doing a complete model of all seven layers, rather, just that you decide up front what functionality you are going to be missing. (And not everyone likes layered models.)

For instance, you're talking about ports. But in a wireless network, the interaction between antennas is fundamentally different from wires and ports. It may be analogous in many regards, but modeling it would impose a different set of constraints.

I don't know much about networking, but based on what you say I agree the easiest way to handle this is to add a column to the Port table for LAG.

If you wanted to be more proper you would create a table that connected NE to LAG, but I don't see a good reason for it based on what you have said.