Help inserting IP's into a previous sql db

[Trip-Out]

So I have a program we use that maintains all of our IP addresses. I need to import new ip addresses into this db which in itself would be pretty easy except for the DB's tables.....

 

I need to find out if an IP address is within the correct subnet and find the subsequent subnet id.

 

I'm probably not explaining this properly.

 

Here's how the tables are layed out

 

Subnet

|-> id              (this ID is referenced by the subnetID in the next table)

|-> subnet      (in ip2long format)

|-> mask        (cidr format)

 

ipaddresses

|-> id

|-> subnetID  (matches up with the subnet table)

|-> ip_addr (in an ip2long format)

 

I need to be able to insert an ip say 10.0.0.25 and ensure that it get added to the right subnetID.  Mind you there can be multiple /29's or a single /24 etc....  

 

Please let me know if you need any more information or if I should clarify this some more.

Edited June 2, 2015 by Trip-Out

[requinix]

Why do you need the subnetID at all? You can always look up the appropriate subnet whenever you want, and without having to care about managing a subnetID column. Assuming there aren't overlapping subnets.

 

Store the start and end of the subnet (with an index on the two) to make it easy to JOIN:

JOIN subnet ON ipaddresses.ip_addr BETWEEN subnet.start AND subnet.end