Creating a linking table?

I'm trying to create a database for the circuit records at my office. I have imported all the equipment id's/locations/etc into 8 tables (one table for each type of equipment). Now I need to create another table that will contain the information about which ports are connected to one-another.

Connections may be made between records in any two tables or even between any two records within the same table. And to add some complexity, any circuit that connects to the EC (echo canceller) will need to show four link points.

My question is really quite simple: How the heck to I make this table? Whatever the method, it is important that I be able to take information from the excel workbook that all this info is currently in and regex it into a format that will make the entries in this new table automatically. I've got about 10,000 circuit end-points just for the DS1 part of this project so I /really/ don't want to re-enter all this manually.

The ultimate goal of this project is to have a web page that allows any user to query a port and see where it's going and for certain users to be able to make/break/change connections.

Unless there is a real need to keep these in separate tables, I recommend you make this even simpler, and store all equipment ids in one table, and then have a column for 'type'. You might even want to validate this column against a 'type' table with a foreign key constraint.

If there is some extraneous information about some equipment types that is too different from others, that information can be kept in another table, linked via the equipment ids.



Now, I'm not totally sure what your needs are, but as I understand it, you have many pieces of equipment of up to 8 basic types, and each piece of equipment can have many ports, right? So, you will need a 'ports' table with a many-to-one foreign key reference to the main equipment table. Then, you can enumerate as many ports for one piece of equipment as you want. You will want some sort of id column so that every port can be tracked uniquely (let's call it 'port_id').

Then, since (except for the "echo canceller", unless I miss my guess) any single port can only connect to any single other port, you don't need a many-to-many" linking table, but a "one-to-one" linking table. Create a table with two columns, each column referencing the port_id column of the ports table. Now, make the first column the primary key, and add a UNIQUE index to the second column. Now, each port can only be placed in this table once. NOTE: this is still not enough for complete date integrity, because think about this:

This would mean that port 2 is connected to both port 1 and port 5. So, if you want to guarantee integrity, you need a trigger which doesn't allow a number in the port_from column if it exists in the port_to column. (I know, the naming conventions could be better )

For the echo canceller, you should create another table, which has one 'port' column and four link columns so that any connection to an echo canceller gets four links. Again, you will want a trigger that checks the main port_linking table, so that ports are not duplicated from one to the other.



Now, without looking at this spreadsheet, there is no way to tell you how it can be done. At least, post a small fragment of the data, so we can see how it is organized. In fact, I may have totally misunderstood your logical requirements, so maybe my table design in general isn't the right one. Please respond with more info.