Database design and query problem - help is aprreciated

[Jan]

Hi all,

I can use some help with the following application. I hope some of you can help.
I think the table-design is OK, but remarks are welcome.

The situation is as follows:

- We have a large site with lots op so called Concentration Points (CP's)
- Fiber optic cables connect the different CP's, so each cable has it's starting
point, it's endpoint, and a certain length
- Each cable consists of different pairs
- In a CP, a pair from a cable can provide an uplink for some active equipment
(switch) in that CP, or a pair can be patched to another pair, which goes to
another CP at it's turn.

I have the following tables:

- ContentrationPoint (CPID, CPName)
- Fibers (FiberID,FiberName,CPID1,CPID2,Pairs,Length) with CPID1 the starting
point, and CPID2 the endpoint
- Pairs (FiberPairID, FiberID, Pair, UsedInUplinkFrom)
- Switches (SwitchID, SwitchName)
- Patches (PatchID, PairID, PatchedToAtCP1, PatchedToAtCP2)

I can make the forms to enter data for the fibers, the pairs, etc., which is
quite easy, but I want to have a form to enter the patches like this:

- I choose a pair (which is a conbination of FiberName and Pair)
- Then i get a list (drop-down) of all possible other pairs where it can be
patched to at CP1 (corresponding to the table Fibers), and the same at CP2.
Pairs at that certain CP which are already connected to an other pair, should
not appear.

I would like to have a query for this, and I also don't know immediately how I
can make the data for a dropdown in field 2 depend on the data in field 1 of
record A.

Thank you very much.

Jan

 

[Tom Ellison]

Dear Jan:

This looks like a good start for a table design, but there may be some
places to improve it.

In the Fibers table you have CPID1 and CPID2. This could be a problem
for queries later on. I would suggest it MAY be a better design to
have a separate table of endpoints.

Endpoints (FiberID, CPID)

In this table, there would be two entries for each fiber, representing
the two CPs joined.

One potentially very important question would be whether a fiber
represents a directed segment or not. If signals travel equally in
both directions, then it is better not to do anything to assign a
direction to it. The above table suggestion does this. When
reporting, however, it is inevitable that you must put them in some
order, such as having a report showing the two ends, one end in a
left-side column and the other end to the right. Sorting them by CPID
would be sufficient to do this and to do it consistently.

If, on the other hand, you wish to assign a direction to each fiber,
add another column to the above table which would contain two
integers, perhaps 0 or 1, to assign one end as the start and the other
as the end.

Why is this a good idea? Let's say you wanted to display all the
connections at a given CP. To do this with your current design, you
would need two SELECT queries, one for CPID1 and one for CPID2, or you
would need a more complex criteria statement. This is not a big
thing, but when you need to build more complex queries any extra
complexity in your basic queries will be multiplied.

This does present a bit of a problem in the user interface. Instead
of picking a CP1 and a CP2 for each fiber, you might need something
more complex. I'm thinking this might be a multi-select combo box,
for example. When it comes time to update this to the tables, you
would need to ensure that exactly two CPs are selected, no more, no
less. This would be sufficient to maintain integrity of the database.

I'll wait to hear from you on this before trying to proceed from this
point.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Jan]

Dear Tom,

Thanks for the answer.

The signal flow has not really a direction. You can maybe think of an uplink
from a central router to a local switch, but is possible that the traffic flows
in the opposite direction. Think of it as ordinary ethernet network cabling.

It is important that, when you 'patch' a pair to another pair, on the other side
half of the patches that were originally available, will 'disappear' because we
are now at the 'other end' (is this clear? I hope so...).


When I read your comments, I also thought of a different way about the
Patches-table. In my original question, it described a pair (of a fiber), which
was at side one patched to another pair, and the same at side two (PatchID,
PairID, PatchedToAtCP1, PatchedToAtCP2).

Another way to look at this, is that the Patches-table contains patch-cords,
which connect just two pairs at a certain CP (PatchID, PairID1, PairID2).

I don't know what will be the easiest solution.

So, to answer your question, the direction doesn't matter, but I don't know
exactly what you mean with the multi-select combo box (a combo box that makes it
possible to select more than one value?).

Regards,

Jan

 

[Tom Ellison]

Dear Jan:

Yes, a multi-select list box is on in which you can select any
combination of rows. For me, the only reason to use a list box is
when I wish to allow more than one selection. For one selection only,
a combo box is suficient and more convenient (uses less space on the
form).

So, I was thinking this could be used to specify the end points for a
fiber. If the user must pick exactly two CPs, then they could not
create a loop (both endpoints the same CP). This is something you may
wish to prevent.

However, coding to insert or update the database for the two
selections would not be extremely simple, nor would the job of
displaying the current selections when navigating through fibers.

The real crunch would be its usefulness as a user interface. If there
are a very large number of CPs then it may not be convenient.

I did think this would be a worthwhile think to think about early on
in the project. Table design and user interface are generally a good
portion of the work in a project, because correct decisions about
these have a great influence on the time it takes to perform the
project and the quality of the finished product.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Jan]

Tom (and maybe others),

Thinking a bit more about it, I think the solution with
the table 'Patches' (PatchID, PairID, PatchedToAtCP1, PatchedToAtCP2)
can be excluded, because then a patch would require two entries in the table,
namely:

- ID, pair1, pair2, pairx
- ID, pair2, pair1, pairy

Or in words: if pair1 is patced to pair2, then pair2 is also (obviously) patched
to pair1. I is not desired that the information is entered twice, I think.

I think it's better to go for the alternative solution where the table 'Patches'
describes a patchcord which connects two pairs:

- ID, pair1, pair2

The CP where the patch is made can be found as the common CP of the fibers where
pair1 and pair2 belong to.

I think this determines the layout of the table 'Patches', if not, please comment.

Now I still don't know what's the best way to create the drop down menu for
entering data in the table 'Patches'. I have something in mind like this:

- I open the form, and see 4 columns:
- ID
- A dropdown for CP the where I want to make the patch. This data is
is not entered in the database, this just filters the data for
the next column
- A list of available pairs in the CP I chose (it would be nice to
see only the 'free' pairs in that CP, i.e. the pairs that are
not yet patched at this CP (but maybe on the other side of the
fiber)
- A similar list of 'free' pairs in that CP, but if possible
only pairs that do not belong to the same fiber of the pair
chosen in the point above (as this is quite useless)

Any suggestions?

Thank you very much,

Jan

 

[Tom Ellison]

Dear Jan:

I was not suggesting you would represent CPs for pairs, but for
fibers, which would not create the unnecessary duplication of data you
propose as a problem.

Given the information in your latest post, a pair can only be
connected to the two CPs at its ends, right? So each pair is either
connected, or not. This suggests a boolean value in the table for the
pairs.

I would tend to use a sub-form to show the available pairs, with a
check box where you could set the boolean value whether that
particular pair is connected or not. I think you would wish to see
only those pairs not connected in some circumstances, and be able to
see all the pairs at other times. For this I would place an unbound
check box on the form to toggle the logic on the form between these
two states.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts