One table populated from two tables keeping relation

[nande123]

Hi,

First I must say I'm new to database and Access so please have patiance with me.

Trying to build a security exemption DB and the first category of exemptions I'm working on is firewall exemptions.

A firewall rule contain either a single protocol (HTTP, HTTPS etc) or a combination of protocols like WEB including both HTTP and HTTPS.

So I have created following tables,

tblGroupProtocol
GroupProtocolID
GroupProtocolName

tblProtocol
ProtocolID
ProtocolName

tblProtocolGroupProtocol
ProtocolID
GroupProtocolID

tblFirewallProtocol


And my question is how can I get tblFirewallProtocol to include both ProtocolID and GroupProtocolID in one field or are there any better ways to manage this?

The end gole is to have a drop down list to chose from when creating new firewall exemptions which contain both Group and Single protocols.

Regards,

 

[MajP]

You already have a junction table in that you have a tblProtocolGroupProtocol,
but if a group protocol is nothing different than a regular protocol except it has related records you can eliminate the group table.

So instead of a junction table that links items in the protocol table to items in the group table. You make a junction table that links items in the protocols table back to items in the same protocol table.

 

[nande123]

Yes a group protocol is just a list or a group of protocols.
But since a protocol could be a member of several groups I guess I need to use a juntion table?

I made a few chaneges and this is how it looks now,


tblFirewallProtocol
FirewallProtocolID - PK
ProtocolName

junGroupFirewallProtocol
GroupProtocolID (linked to PK in above table,Include ALL records from 'junGroupFirewallProtocol' and only those records from 'tblFirewallProtocol_1' where the joined fields are equal. )
FirewallProtocolID (linked to PK in above table, Include ALL records from 'junGroupFirewallProtocol' and only those records from 'tblFirewallProtocol' where the joined fields are equal.)

Is this correct?

 

[nande123]

Think this is solved now.

Have one table called tblFirewallProtocol and one called junGroupFirewallProtocol.

Both fields in junGroupFirewallProtocoll have relation with PK in tblFirewallProtocol.

Big thanks to all of you helping me with this problem.