Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Can you please explain the questions again? I do not understand what you are asking. I know English is not your first language, and I apologize since you are far better at English than I am in any second language, but if you can clean up the question's grammar (perhaps with the assistance of another local developer who has more experience with English) it would help us answer it. –  Myrddin Emrys Mar 28 '12 at 14:40
    
If the data is mostly numeric, this means you will loose some data. Also, this change will affect your applications. What will you gain by making it numeric anyway? I suggest you study this before you do it. –  Emmad Kareem Sep 4 '12 at 22:37
add comment

1 Answer 1

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.