How to Remove redundancy using normalisation?

Question

I have XML in which all the customer information is stored. Whenever my PHP code runs, it inserts the customer data from XML into the database. Here is the table. Not all the customer are web, so I have saved all the customer information directly to one table called "customer".

Customer:

customer_id             int(40)         primary key 
CustomerAccountNumber   varchar(60)
CustomerAccountName     varchar(255)
AccountBalance          double      
InvAddressLine1         varchar(500)
Rep                     varchar(20) 

To store "web customers" login and usernames, I have a different web account table called "Web_customers" that has a "CustomerAccountNumber" as the primary key.

Web_customers:

web_id                  int(11)         primary key
UserName                varchar(39)     
CustomerAccountNumber   varchar(39)
EmailAddress            varchar(255) 
Pwd                     varchar(70)     
customer_type           varchar(10)

Sample data:

 customer_id  CustomerAccountNumber  CustomerAccountName   AccountBalance InvAddressLine1   Rep
 1                Accnt1           Myname1                 1098             address1       Mik 

 2               Accnt2           Myname2                  2398          address2        Richi

 3               Accnt3           Myname3                  2234398       address3        Santa

 4               Accnt4           Myname4                  2233398       address4        Den

Since I run the PHP script to reload data continuously the customer_id changes frequently and that is why I can't choose it as foreign key in my other table which is here:

WEB_CUST:

 web_id     UserName    CustomerAccountNumber EmailAddress    Pwd     customer_type 
     1      Accnt1      Accnt1                [email protected]     123     simple
     2      Accnt212    Accnt2                [email protected]    123     complex
     3      Accnt313    Accnt2                [email protected]  123     complex
     4      Accnt315    Accnt2                [email protected]  123     complex

I have two type of customers, "Simple" and "complex". Simple customers have one CustomerAccountNumber and have only one username in the corresponding WEB_CUST table.

Complex customer are those customers that same account number. But the problem is the "complex" customer. When the WEB customer with username Accnt313 and Accnt2 changes the information, it updates the all other customers having the same Accnt2 in my XML table "Customer". NOTE All the fields in the my XML are directly inserted to customer table using insert batch method.Since,my XML had more than 60,000 and parsing and inserting data into child tables could have raised performance issue.That is why,i didn't break it up and that is why i am looking for alternate solution.

How can I prevent this happening? How can I normalise in this case? Is there a way I could normalise without disturbing the main XML table "Customer" due to XML parsing efficiency and reloading reasons?

Answer 1

The system is working as you've designed it.

If you want a each web customer with common account code to be considered a separate customer with separate billing information and account balance, then copy all the fields from customer into the web-customer table and never change the customer table.

I recommend you restructure your database as follows

AccountCode
    ID
    AccountCode


Customer
    ID
    AccountCodeID   (FK to AccountCode.ID)
    AccountBalance
    InvAddressLine1
    Rep

CustomerUser
    ID
    CustomerID    (FK to Customer.ID)
    Email
    Pwd

and parse your XML correctly.