Hi there, i have a database setup that goes like this:

there are several different tables. Rather than linking them all together directly, I created a separate table, called "links", which has 5 fields- the table key, and four fields to record the table name and key of the source and target tables. The reason i did this was that i wanted to be able to have a single record be linked to multiple records in a single table. So far so good, and everything was working fine. Then today I noticed one of the portals I set up was displaying additional data. After some digging around, it appeared that it was referencing data from the wrong link. It's probably clearer if i illustrate by way of an example:

i have a table called "blue". One of the records has a key of "12". I linked this to a record with a key of "8" in the table "purple". So i have a record in the links table like this:

linkKey: 80

linkToTable: blue

linkToKey: 12

linkFromTable: purple

linkFromKey: 8

And this works and shows up in the portal. But there is also the following, separate record in the links table:

linkKey: 80

linkToTable: blue

linkToKey: 12

linkFromTable: orange

linkFromKey: 9

which it seems is being used to display record 9 from the *blue* table in the portal. This is despite the fact that the relationships i set up are like this:

from links table to blue table:

blueKey = linkToKey AND blueTable = linkToTable

(where blueTable is just a global field with the word "blue")

from purple table to links table:

purpleKey = linkFromKey AND purpleTable = LinkFromTable

etc...

Can anyone shed some light on this? Or should i go back and redesign the DB without a links table?

Well, just to let everyone know, I ended up going back and removing the links table, reworking all the scripts. I've lost the advantages I had before but everything seems to be working, and at least now the scripts are a lot less complicated.