I'm very new to writing SQL scripts and I would very much appreciate a sanity check of what I've come up with.
The tables will back an IP Management solution. There are a total of two tables.
This table is pre-populated with a large amount of IP ranges that are available within the networks in different regions. A range is considered available if the value in the column
This table is populated on demand based on calls made to an API. The call includes any number of desired ranges (e.g.
24). A call will fail if we cannot find any available range with the desired size in the table
AddressRanges. Once the preconditions are fulfilled, the id of the virtual network will be inserted into the FK column of the
Finding available ranges would by done like this:
SELECT * FROM AddressRanges WHERE VirtualNetworkId IS NULL AND CIDR IN (24) AND Location = 'euw';
Reserving a range would be done like this:
UPDATE AddressRanges SET VirtualNetworkId = 'id-of-virtual-network' WHERE NetworkAddress = 'address-received-from-select-query' AND Location = 'location-of-address';
Freeing a range would be done like this (or should I use
UPDATE AddressRanges SET VirtualNetworkId = NULL WHERE NetworkAddress = 'address-reserved-by-virtual-network' AND Location = 'location-of-address';
And finally deleting the network entirely:
DELETE FROM VirtualNetworks WHERE SubscriptionId = 'subscriptionId' AND ResourceGroupName = 'resourceGroupName' AND VirtualNetworkName = 'virtualNetworkName';
If a virtual network is deleted, the FK column in the
AddressRanges table should be set to
GO -- A full resource identifier for a virtual network is: -- subscriptions/<SubscriptionId>/resourceGroups/<ResourceGroupName>/providers/Microsoft.Network/virtualNetworks/<VirtualNetworName> CREATE TABLE [dbo].[VirtualNetworks] ( -- We use the unique network id as a foreign key [VirtualNetworkId] UNIQUEIDENTIFIER UNIQUE NOT NULL, [SubscriptionId] UNIQUEIDENTIFIER NOT NULL, [VirtualNetworkName] VARCHAR(64) NOT NULL, [ResourceGroupName] VARCHAR(64) NOT NULL, -- A virtual network is unique based on its name within a resource group CONSTRAINT AK_VirtualNetwork UNIQUE(VirtualNetworkName, ResourceGroupName), -- The virtual network id is globally unique CONSTRAINT PK_VirtualNetwork PRIMARY KEY (VirtualNetworkId) ); GO CREATE TABLE [dbo].[AddressRanges] ( -- This is null until the ranges is taken by a virtual network [VirtualNetworkId] UNIQUEIDENTIFIER, -- The first address in this range -- This is always the start of the range +4 reserved [FirstAddress] VARCHAR NOT NULL, -- The last address in this range -- This is always the end of the range -1 reserved for broadcast [LastAddress] VARCHAR NOT NULL, -- Number of available addresses, excluding 5 reserved by Azure [Count] INT NOT NULL, -- In which region this range is available [Location] VARCHAR NOT NULL, -- The staring address including CIDR [NetworkAddress] VARCHAR NOT NULL, -- The CIDR [CIDR] INT NOT NULL, CONSTRAINT FK_VirtualNetworks_VirtualNetworkId FOREIGN KEY (VirtualNetworkId) REFERENCES dbo.VirtualNetworks (VirtualNetworkId), -- An address is always unique within a location CONSTRAINT AK_VirtualNetwork UNIQUE(NetworkAddress, [Location]) );
Based on the information given, is this a sensible design? If not, what would you change?