Firewall request application correct database format

I'm writing a Firewall Request application.  Users can submit a request to modify or add an rule in the firewall.  It asks for source ip(s), dest ip(s), source port(s), dest port(s), port(s), and protocol(s).

I'm curious about the best design and best practices for the database.  Because there can be any number of source IP's going to any number of dest IP's, I have a link table called FirewallSourceAndDestIP that is below.

CREATE TABLE FirewallSourceAndDestIP
(
	ID int NOT NULL IDENTITY (1,1),
	FirewallRequestID int,
	SourceIP Nvarchar(16),
	SourceType Nvarchar(20),
	DestIP Nvarchar(16),
	DestType Nvarchar(20),
)

Open in new window


Making a row for every source to dest connection and it links back to the main FirewallRequest table.  However, when it comes to ports, I'm confused at the best way to accomplish this same task.  If I use a link table, it's possible I could have up to 65k+ rows per request if they want all ports (any/any) connection.  Is this the best way to go about it, or am I missing an easier solution?
RIP_LeroiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark BullockQA Engineer IIICommented:
Can you add port to your table and leave it null to mean all ports?
0
RIP_LeroiAuthor Commented:
I've considered that but there are also a lot of request that have 10k+ ports (lots of high UDP ranges for audio/video).  Does this even matter?  Am I over thinking it?
0
Mark BullockQA Engineer IIICommented:
How would someone enter 10,000 port numbers? Maybe you save ranges.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Create a similar table but only for Ports:
CREATE TABLE FirewallRequestPort
(
	ID int NOT NULL IDENTITY (1,1),
	FirewallRequestID int,
	PortNumber int,
	PortRange int
)

Open in new window

So:
- if PortNumber is NULL then means is open for ALL ports
- if PortRange is NULL then means is open only for the PortNumber
- if PortRange is not NULL then means is open for the range PortNumber-PortRange
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RIP_LeroiAuthor Commented:
This works perfectly.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.