many to many and more

[rivet]

I have a list of town and some of the need to be linked together. So if I I am viewing town A and town B and C need to be linked to it, I will need to see that A and C belong to B when I am in that record and when I am in C I will need the portal to show me that A and B are related.

So if a town is related to two other towns there would be six relationships in a 'join' file.

A town related to three town would have nine relationships.

How can this been done?

Note I have considered that the records share a common 'join' id but that would be plan B

[RalphL]

Towns are in one table so this is a many to many self-relationship. This looks like 2 tables are all that are required. The table Towns and the join table.

[Ender]

A many to many self join would work if you only need to know that they are linked. But if you need to know the mileage between them, or other information about the route, then a Town table and a Route or Link table is necessary.

I've been working on a database for exactly this type of relationship. Below is a TOG of how I have mine linked.

The only difficulty with this, is that the joins are one way. To make a link in the opposite direction requires a little scripting.

[RalphL]

Yes, that is what I had in mind. If you script the creation of the record in the join table you could create a second record for the reverse direction. A to B and B to A.

[rivet]

It does boil down to scripting but can you imaging when there are 5 or 4 town joined. Thanks guys

[RalphL]

No, I can't see what you are trying to do. I can see A joined to B, C, D & E. Each of these is a record in the join file. Are you saying that since A is joined to the other 4 that they must be joined to each other?

[Ugo DI LUCA]

Hi,

If they must be linked to each other, then you'd need an another "Universal" ID, and base your relationship upon it.

It is still one relationship though, whatever the number of related records are.