Mysql – Handling newsletter mail subscribers data

database-designMySQL

A simple data structure for subscribers is email, name, phone, building.

Currently, Building is a VARCHAR field, however data contained in the field is actually mostly numeric. How can I change the field type to INT without losing data?

Also, if a subscriber sends a request for newsletter, with name, phone, address in the request, and he need to click on a confirm link for the request, what will happen if before he clicks the link, the data structure is changed? For example, if the phone field is deleted, when I insert the new subscribers data, there will be problem because the database structure no longer matches. How can I fix this?

Best Answer

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.

Related Question