MySQL – Storing Mail in Database, Best Data Type

Don't store as a string. Use an int unsigned column and store/retrieve with INET_ATON() and INET_NTOA() respectively. AFAIK mysql doesn't support INET_* for ipv6.

EDIT as per comment

Using built in function to converto IPs to/from integers (and so storing those integers in the database) has the side effect of automatically validate those IPs. Say you store an IP as a VARCHAR(16), you have to make sure not to store invalid IPs (like 999.999.999.999 as an example) with some custom validation. INET_* functions take care of that.

For your first problem, you may have to do it in stages. Here's one approach:

1. Create the new INT column - BUILDING_ID_INT
2. Create a new table that maps the building name text to Integer values
3. Write a program to populate the new BUILDING_ID_INT column based on the data in the current Building column and the values in the table created in #2.
4. Re-write all of your existing code to use BUILDING_ID_INT.
5. Delete the old Building column.

For the second problem, where the database structure changes between the time a user makes a request, and the time they confirm their request, it will vary on the kind of change. If an old column is being removed, then there shouldn't be any problem because your current insert statement should not be inserting data that's not insertable. For new columns that are added, default values can be used of it's non-nullable, and process will have to be introduced to handle these. For small volumes, it can be manually asking these users to send the additional new information. For large volumes, you'll want to write code.