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,

 

[Remou]

Is there any reason why you need two tables for protocols?

http://lessthandot.com

 

[nande123]

Not sure,

Wanted to have one tabled for group protocols which keep track of what protocols it includes.

Can I do it in another way?

 

[Remou]

It seems to me that you can have one table with a flag for whether it is a group protocol or not. A query will quickly sort between the two:

SELECT ProtocolID, ProtocolName 
FROM Protocols
WHERE GroupProtocol = True '' Or False, depending

http://lessthandot.com

 

[nande123]

Ok, how would the tables look like then?

Guess I need a new table to list what protocols belongs to a group protocol?

 

[Remou]

No, just a flag.

tblProtocols
   ProtocolID     <-- Primary key
   ProtocolName   <-- Text
   GroupProtocol  <-- Yes/No

http://lessthandot.com

 

[nande123]

Not sure if I missunderstand you.

Lets say we have defind three protocols,
HTTP
HTTPS
WEB

in tblProtocols and WEB has the flag set to Yes.

Now how do I say WEB should include HTTP and HTTPS?

 

[Remou]

Okay, please post some data from existing tables, because I cannot see why WEB does not already mean HTTP & HTTPS, because from your description, it is just a name. It may be that you need a table that is back up a step:

tblUpOneStep
ID 
Type <-- Single or Web

tblProtocols
TypeID <-- FK to type
Protocol

http://lessthandot.com

 

[nande123]

Not sure if this picture will help.

This is how it is done now and the table ExemptionFirewall include two fileds FirewallProtocolID lookup for single protocols and GroupProtocolID lookup for group protocols.

If possible I would like to replace both these fileds with one that contains all protocols (single and group).

The example with WEB was only one example there could potentially be 100's of goups which include a mix of protocols.

 

[Remou]

I do not see in the picture where it shows HTTP & HTTPS for a group protocol, so :
"how do I say WEB should include HTTP and HTTPS?"

How do you do this at the moment?

As a general rule, if you want to join two tables together for some valid reason, they probably should be contained in one table, and from the picture, I believe this can be done, if you will answer the above question.

BTW, even a small amount of data can be more helpful than any number of pictures, in this case, a picture does not paint a thousand words

http://lessthandot.com

 

[nande123]

First time I'm posting an issue so I'm not yet up to speed on how to best explian my problem.

HTTP and HTTPS would be two different rows in tblFirewallProtocol and WEB would be a row in tblGroupProtocol.

Not sure if I have answered your question now?

 

[Remou]

So what is wrong with a table like so:

[tt]
ID ProtocolName ProtocolTypeID PortRange GroupProtocol
1 HTTP 30 198 N
2 Web 21 Y
15 Blah 2 Y
[/tt]

Or even

[tt]
ID RuleName ProtocolTypeID PortRange HTTP HTTPS
1 Gloop 30 198 Y
2 Web 21 Y Y
15 Blah 2 Y
[/tt]

The first example seems fairly similar to what you already have, and avoids lists of fields that occur in the second example (HTTP, HTTPS) which is often frowned upon, but you might get away with it in this case.

http://lessthandot.com

 

[nande123]

I tried to avoid the second option as I don't know how many protocols there will be and it will grow over time.

What it ProtocolTypeID pointing to?

 

[Remou]

You tell me, you have a protocol type table with an ID and name in the picture. Without sample data, I have no idea what it holds or even if it is necessary.

http://lessthandot.com

 

[nande123]

Does this help?

 

[Remou]

Are we getting any where?

http://lessthandot.com

 

[nande123]

Did you check my attached DB?

 

[Remou]

Yes I did, and I do not see where you are having problems with my suggestions, so I would be glad if you would say what is the problem.

http://lessthandot.com

 

[MajP]

Lets say we have defind three protocols,
HTTP
HTTPS
WEB

in tblProtocols and WEB has the flag set to Yes.

Now how do I say WEB should include HTTP and HTTPS?

If have not looked at your db. But if a "protocol" can have other protocols related to it then it can all be done in a single table. I do not know what you are putting in for group, but if the group is simply the parent protocol then you can use a single self referencing table and do not need any group tables.

tblProtocols
ProtocolID
ProtocolNam
parentProtocol

data:
1 Web
2 HTTP 1
3 HTTPs 1
4 XYZ
5 X 4
6 Y 4
7 Z 4

This says the following
Web
HTTP
HTTPS
XYZ
X
Y
Z

However, if X and Y are also in the web group then you need a junction Table and cannot store the parentProtocol in the tblProtocol. However you can still have a single protocol table.

juncTblProtocalParentProtol
protocolID
ParentProtocolID

Data
ProtocolID ParentProtocolID
2 1
3 1
5 4
5 1
6 4
6 1
7 4

This would define the relation
Web
HTTPS
HTTP
X
Y
XYZ
X
Y
Z

This can be used further if you have relationships like
Web
HTPPS
abc
def
HTTP
X
x1
x2

I have been demonstrating this concept a lot lately. If interested
thread701-1616534

 

[nande123]

Hi,

Yes a protocol can be a "member" of several groups so I guess a junction table is needed.

Think I understand how to design my tables now just have to figure out how to design my for after this new setup. Will come back with further questions if I run into proble.

Will also take a look at you attached link.

Thanks for all help I've got here!!!

Regards,