Restricted: Multiple n-to-1-relations between two tables

[Harry Boeck at LBD]

Hallo,

before treating this as an error, I would like to request the possible ideas behind this behaviour...

I have a table with two fields pointing to another table. Both fields have different meaning inside the table where they are defined, but refer to the same type and content of external data.
In detail, I have a table of happenings between traders, where one trader is the seller, the other the buyer. Both traders belong to the same pool, that's why they should reside in the same table. For both parts, this should be an n-to-1-relation from the table of happenings to the table of traders.

OpenOffice doesn't let me define this relation. It tells "Diese Relation existiert bereits. Möchten Sie sie bearbeiten?".

What I can do to circumvent this, is to introduce a redundant intermediate table, having for example the n-to-1-relation to the happenings and a 1-to-1-relation to the traders.
But this doesn't look "nice", does it?

Is or was there something intended for this behaviour to occur?

Thanks for your interest.
Harry Boeck

[DrewJensen]

Harry Boeck at LBD,

If I follow you then I would not call this an error.

The question is how are you representing 'A happening' - I'll call it a trade.

You have a table of traders.
maybe like TraderID, TraderName.....

You have a table of happenings.


Having built tradeing systems in the past I am used to seeing a table with something like (but not always)

HappeningID, TraderOffer (FK TraderID), TraderAccept (FK TraderID)....

So we want to foreign key constraints from Happenings to Trader. In this case each FK is not using the same set of fields. There was an issue with an earlier build in Base that would not allow this to be created using the relationsip dialog. But you could still create it issuing the actual create constraint DDL command to the engine via the SQL window.

The issue was marked as fixed, and I thought it was incorporated in 2.0. Perhaps it is due out in 2.0.1, next month.

Of course if this is not how the tables are defined then this would not hold.

If you have it set up so that each TRADE uses two records in the Happenings table - one representing the Offer and the other the Acceptance each points to Trader table.

Does any of this make sense?

Drew

[Harry Boeck at LBD]

Thanks!

I will look at "create constraint".

As it is, the relationship dialog is still having that issue, you mentioned.
And thanks for preventing a redundant post to the bug tracking system.

Harry Boeck