Database structure : IP Address

Anon

Hi all,

I am looking for a clean way to store my customers IP address pools in a SQL database. The only way I have found so far is :
Table IP
- ref_ip bigint
- address string (containing 10.0.0.1)

Table Customer
- Name string
- Blah string
- ip_address string (containig a serialized array of all the IP addresses used by this customer).

It does not work well in particular if I want to search customers by IP address.

Anyone has an idea ? It should work with a random number of IP address per customer, being easilly searchable, and if it could follow the database normalisation standards that would be great (coffee too ? No thanks).

Thanks all.
Best regards,
Maniak

mattr

What is 'ref_ip' in IP?

What I'd do is this:

Table 'customer':
Name --> primary key?

Table 'IP':
ref_ip
CustomerName
address

That way to see all the customer's IPs you can go:
SELECT *
FROM IP
WHERE CustomerName = 'Bob Ross'

And since it is a single varchar column you can index it and perform querys on that.

Anon

If you're using PostgreSQL, it has an indexable IP address type.

Chris

Anon

Hi,
Sorry but I did not mention that the table Customer had a primary key called ref_customer (bigint) and a key name with the customer's name (among many other things).

We could then have a table IP like this :

Table IP
. ref_ip (bigint primary key)
. ref_customer (bigint)
. Address (string containing the ip address).

Does this structure respects database normalisation ?

Anyway thanks a lot for your help.

Best regards,
ManiakBob