best practices of multiple relationships between tables

[desklamp]

Access 2003 on Win2K

The tables involved:
an IP address table, tblIP, where basic IP address information is stored: IP, WHOIS, reverse DNS name, etc.

an event IP table, tblIPEvent, where event specific data is stored: IP, event number, port, protocol, direction, etc.

a third report table, tblReport, where I track the source IP, destination IP, event number, and and additional information provided by analysts.

The relationships established (so far!):
There is a one-to-many relationship between tblIP and tblIPEvent, since a single IP address could be involved in many events.

The Desired outcome:
I'd like to create separate (one-to-one) relationships between tblReport.(EventNum,SrcIP)<=>tblIPEvent.(EventNum, Address) and tblReport.(EventNum,DstIP)<=>tblIPEvent.(EventNum, Address).

The Questions:

• In the Relationships Manager window, should I define both these relationships in the one "tblIPEvent" table; or keep only one relationship per table and create another table, "tblIPEvent_1"?
• If "tblIPEvent_1" needs to be created, do I also need to (re-)create a one-to-many relationship between the original tblIP and the new "tblIPEvent_1" or create a new "tblIP_1" for that one-to-many relationship?

Please let me know if this question is not clear, or how I can better describe my situation to assist the experts. Also, corrections to my methods or table layout also accepted!

Best Regards.

[Scott Price]

As I read through your post, assuming that your Event is something on the order of a MS Error Message/number or a generic Collision type name, it looks to me like you may have a Many to Many relationship between tblIP and tblEvent.
To test this, let's ask this question: It is true that One IP can be involved in Many Events, but can the same Event be matched to more than one IP? (The existence of your SouceIP and DestinationIP fields in tblReport makes this almost a certainty in my mind)

You might explore something along the lines of this:

tblIP
IPID AutoNumber PK
IPAddress
WHOIS
ReverseDNS

tblEvent
EventID AutoNumber PK
EventDesc

tblIPEvent
IPEventID AutoNumber PK
EventID FK
IPID FK
Port
Direction
IPInvolve (i.e. Source, Destination)
Analysis

Let me know if you don't follow the logic behind this, or if I'm assuming wrongly!

Regards,
Scott