Re: Help with the best table layout for message board

[Rick James]

Normalizing a not-too-long string is usually not worth doing. However, let me bring up several other issues here.

Have you heard if IPv6? It is a now IP standard; it will be rolling out 'soon' (next year?). It involves 128-bit numbers instead of 32-bit numbers. The format, etc is quite different. VARBINARY(39) for string representation, or BINARY(16) for purely numeric is advised.

Will you be doing only exact matches on IP addresses? Then you should consider turning each IP address received into a canonical form. This will be even more important with IPv6 because there are multiple equivalent forms.

If you need to deal with ranges (CDRs), do not normalize; performance will be terrible. Instead, pick a binary format.

Meanwhile, for IPv4, INET_ATON() and INET_NTOA() may be useful.

Do you want a table of banned IPs (black list)? Or a whitelist? Or a table with all IPs, and the status of each? How will banning an IP interact with the other tables? Will this be dealt with as you make an entry (efficient, but takes extra code)? Or on every SELECT (not as efficient).