Re: Database design and query problem - help is aprreciated

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Jan (boeckx_jan_at_yahoo.com)
Date: 10/12/04 

Date: Tue, 12 Oct 2004 09:01:09 +0200

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 wrote:
> 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
>
>
> On Mon, 11 Oct 2004 22:55:51 +0200, Jan <boeckx_jan@yahoo.com> wrote:
>
>
>>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 wrote:
>>
>>>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
>>>
>>>
>>>On Mon, 11 Oct 2004 22:08:40 +0200, Jan <boeckx_jan@yahoo.com> wrote:
>>>
>>>
>>>
>>>>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
>>>
>>>
>

Relevant Pages

  • 9_Recommended error codes (specifically return code 5)
    ... * "return code 2" indicates patches are already installed. ... * "return code 25" means a patches requires another patch that is not yet installed. ... With or without using the save option, the patch installation process ... Installing 114008-01... ...
    (SunManagers)
  • Re: [PATCH] ext4: memory leakage in ext4_mb_init()
    ... one of the important parts of a patch is that the patcher ... If you are not able to deal with all patches received, ... stock kernel, is buggy, and end users have to face panic. ... Ext4 is actually quite stable at this point. ...
    (Linux-Kernel)
  • Re: Mugello (spoila)
    ... Tom, either that's an amazingly stupid thing to do or you're being ... I get the feeling that you don't mind the Euro's(whoever ... Didn't remove the concrete patches? ... was there during the work, saying, "No, you gotta make those damned curbs ...
    (rec.motorcycles.racing)
  • Re: This is [Re:] How to improve the quality of the kernel[?].
    ... The -mm kernel already implements what your proposed PTS would do. ... If patch have no TS ID, ... Thus i can apply for example lguest patches and implement and test new ... How many open source projects use Bugzilla and how many use the Debian BTS? ...
    (Linux-Kernel)
  • Re: ATTACK of the WEEK-fentanyl patches
    ... FDA warns of deaths from fentanyl patch ... Some of the deaths came after doctors prescribed the patches to the ... The drug is only for chronic pain in people used to narcotics, ...
    (alt.support.chronic-pain)