hello,

I am new to making databases. I am writing a database for making wire lists for cabling. I do not know what the best way to create this is (I am using MS Access 2003). I would like to have a table (list of wires) as follows:
example connector 1 is connected to 2, and 5 is connected to 3.

Source Destination
A1 ---- C2
A5 ---- C3
A1 ---- A5

My problem is that for example for the first wire, connection 1 can also be the destination when connection 2 is the source.

I would like to make a query that lists all the "A"s as the source and allows you to update the destination column.

Any help is appreciated.

Not sure where this belongs. Not quite sure what the question is either.

I'm popping it in db design. Any mod disagree please move as you see fit.

pootle flump ur codings are working excelent.

Could use more explanation of the application.

Sorry the message wasn't clear. I'm not quite sure what to ask. I'll try to explain it better.
I have some jumper wires and connectors with many pins. I want to make a database that records how the connectors are wired together. For example, I have connector A, B, and C. Connector A, pin 1 connects to Connector C pin 2. Conn A pin 5 connects to C pin 3, and A pin 1 connects to A pin 5 etc. there are hundreds to thousands of these connections.

I would like to have some tables (or queries) that list all the wires that connect to a particular connector (as the source). for example, the table that shows Connector A connections would look like this:

(Source) (Destination)
A1 C2
A1 A5*
A2 C3
A3 B2
A5 A1*

In the database, any pin can connect to any other pin on any connector.

As you can see (I hope) that the wire connection between A1 and A5 is listed twice. ie for the connection between A1 and A5, they can both be both a source and a destination.
I also want to be able to make the table (or query) ammendable. ie so that you can add another connection (eg A6 to B3) which would automatically appear in the "Connector B" table.

one solution I thought of is to have single table for example
A1 C2
A1 A5
A2 C3
A3 B2

then create a join query (somehow - I just read about these) of the reverse fields

C2 A1
A5 A1
C3 A2
B2 A2

and filter out all results starting with "A".

The problem is that I cannot then add a new "record" to my query, and I do not know which way around do I store the connections: for example A1 to A5, do I store "A1" as the source or "A5"?

I guess my question is how do I store these values in a table and how do I relate the tables together ( or table to itself?)?

to relate tables, yes, you'd use a join

in this case you might not need two tables

simply store the connection with the "lower" pin first

e.g. store A1-A5, but not A5-A1

thank you r937 for your comment.

may I ask how I would then add a new record to the list? In MS access, if i use this JOIN query, it does not let me edit the resulting query, or add a new record to it.

I could just edit the original table, but I would like to be able to change the results of this query, to make it "user friendly" for someone else to update.

for example, the result query is displayed as follows:
A1 C2
A1 A5*
A2 C3
A3 B2
A5 A1

i would like the user of the database to be able to select *A5 and change it to A8 (for instance), and then have the reversed link (A5 A1) also update (I would prob then make it refresh the query)

You are approaching the problem incorrectly. You have to have a Connector Entity, a Connector Pin Entity and a Jumper Entity with a Jumper Source and Jumper Target.

Connector
-----------
Connector ID
Connector Name

Connector Pin
-------------
Connector Pin ID
Connector ID (FK)
Pin Number

Jumper
-------
Jumper ID
Connector Pin Source (FK)
Connector Pin Target (FK)
Jumper Number

If you do it this way you will not have a problem with queries. The backwards forwards stuff is confusing the issue.

and yet i don't see how your design eliminates that issue

hi, still working on this problem. appreciate both suggestions sofar.

the issue which is causing the trouble, is that I need to edit the results, not just display them.

The table with source/target fields are not really working for me, and the only implementation of this method that works like this is if the values are duplicated.
A1 C2
C2 A1

...and then implement some code that updates the opposite one when a value is changed (in a form)

I will probably end up using this method if I can't find another, but then I have a database with duplicate (reversed) fields, which almost defeats the purpose of a database in the first place.

Here's my new analogy of the problem.

First, there is no such think as a source and a target. there are just 2 ends.
for example, in this forum, the posts have our names, dazzler77 and r937. If these were labelled with "myself" and "the other person", we would know who is who, but nobody else would. We are both "ourselves", just like both ends of the cable are the source.

If you were to change a wire, you would go to the connector, remove it and connect it to somewhere else. therefore you always change the source.

so if the source and target are both really just 2 sources ,they should be in the same field. they are both connected via a wire.

So I think that the following relationship might be more appropriate:

Wires
-----
WireID (pk)

Connections
----------------
ConnectionID(pk)
WireID (fk)
EndID[1 or 2]
Pin

There are many wires,
There are many (up to 2)pins that can connect to a single wire.

Question: is this the more correct representation for the database design?

Point taken, rudy.

Okay, dazzler77, I think I see what you are attempting to hammer out.

It looks like it will work, but you will have to identify each wire uniquely.

Dazzler, it looks like you are trying to model pinouts, correct? My suggestion would be to start by identifying the different posible pinout configurations you have. Then ask, could this pinout configuration work for more than one type of connector? db9->db25.

I would personally identify the pinout uses first then tackle the connectors then the actual pins they connect to.

The connections are wiring frames, like what they use for telephone wiring.
there will be a cable (for eg. D25, or CAT5, or D40, or a terminal block) connecting from a piece of equipment to the wiring frame. The frames are in columns called A, B, C, D etc. there are say 200 pairs, or 400 connections on each frame.

There is then wires (jumpering) from one frame to another to route the signal to a particular output frame. Then there will be another cable which sends the signal to another piece of equipment or another frame in another room, and then to another, then to a piece of equipment.

The types of signals are a mix of audio, plc control, dc voltage, serial data etc. Some of them are inputs, and some outputs, and some signals get split and some are combined.

I am trying to model the jumpering from frame to frame. so each point to point is a single wire or a twisted pair in some cases.

we use a spreadsheet at the moment. there is a sheet for each frame, which shows the destination of the signal. The signal can be split, and there is a maximum of 3 wires which can fit on one point in this case.
eg.

source Destination
------- ------------
A133 control signal D22
A134 audio + C181, E22
A135 audio - C182, E23

An example of the problem is that, say we move the audio on A134 and 135 to A10 and A11. that means that as well as changing the entry for the A frame, you also have to edit the destination on the C frame sheet, of C181 and C182 to be A10 and A11, and also the E frame sheet.

There are more than one person changing these and so I would like the database to be able to change 1 value (eg move A134 to A10) and all the other tables update at the same time.

The second reason for the database is to validate these connections. so that you CAN'T create a connection from A10 to C181 and not have a connection from C181 to A10.

If I can get this model right, I will then apply it to the cables connecting to the frames as well.

certus,
I don't mind identifying each wire individually, but I would like this to be transparent to the end user if possible, ie. to automatically create a wire when I create a new connection.

Dazzler, lets take one step at a time.

What type of "frame" are you using? Are you using 66 blocks? If you are, will these blocks always be split or will they always be solid?

Maybe it would be better for you to start by breaking down the wire usage, such as telco, data, audio etc. In a telco senerio, you will never ever have less than 1 single pair of wires running anywhere. I am not really sure why you would be using a single wire other than to jump the connection from one side of the block to the other side of the same block. This would make a difference in the way your database is modeled in the end.

Do your "frames" mix and match wire types? eg: do you have a data and audio wire on the same block? Could you ever have that senerio?

Mostly they are 250 pair, in rows of 10pair . a few connections are an older style where wires are soldered on, some are 50 or just 10 pair.

true, I do kind of jump into things. there are a few telco signals and yes they are only going to one place eventually. there are also 2 wire audio which sometimes get split. (prob not good practice but that is what I have.)
there are also some radios. they have 2 wire transmit, 2 wire receive and then a single wire for press to talk, and a single wire return for squelch.
There are control types, single wire dc voltage switched, which sometimes use a common earth, and sometimes get split to 2 destinations . There are also earth blocks, and common blocks for these signals.

yes they do mix and match.

I guess I could use a single direction for some signals (source - destination) but others have multiple destinations. I think what you are getting at is to have different types of tables for different signals?

I guess if a signal splits, it becomes 2 new signals with a new source and destinations. so maybe the input tables/forms should be different to the output query results? ie group by signal types

is this what you mean?

I think part of the problem here is that the whole concept is a bit confusing. I am having a problem visualizing what you want to do. I have a lot of telephone experience and I can sort of relate to what it is you want to do. What I can visualize in my example are two phone blocks. Those two phone blocks are connected by way of a wire. If I draw this on a piece of paper it becomes a little clearer as how to model this from a telco standpoint.

I personally would not care about the wire itself but care more about the two points that the wire is connecting.

Let me ask you this, when you said earlier:
Why or rather explain HOW can a destination pin also become the same source pin????

If you said that you were trying to jumper from block "A" to block "B" to block "C", I would understand that.

From the standpoint that a single pin on block "A" (source) connecting to a single pin on block "B" (destination) connecting back to the same exact pin on the source block "A", just doesn't make any sense to me. Again, it is my electrical background that is hanging me up.

In the telco industry a tech would jump a pair of wires from a source block to the first destination block.

The first destination block would now become the new source block to a third and final block.

The tech would complete his circuit by jumping another pair of wires from that block to the last destination block.

Are you sure this is not what you are trying to accomplish?