By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,481 Members | 979 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,481 IT Pros & Developers. It's quick & easy.

best practices of multiple relationships between tables

desklamp
P: 5
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.
Aug 14 '07 #1
Share this Question
Share on Google+
1 Reply


Scott Price
Expert 100+
P: 1,384
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
Aug 27 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.