database normalization using CFM

Hi All

I am doing some cleanup on a sales database - one of the todo items is normalizing a coding field. Currently the code field is use to filter clients based on a users filters when they login, allowing to to see part or all of the clients in the database. Some users see mutiple codes others just one code - based on filter when they view the client list. Pretty easy concept.

Now, the problem is such that this coding field has now became tough to manage and it has been decided that it should be normalized to a different table. As is usually the case this should have been done early one but hind sight is always 20-20.

This is what we have now - just a small sample - there are 12000+ records:

Code: PCBUS
ID: 3349

Code: PCBCA, PCBUS
ID: 587

Code: PCBCA
ID: 3350

I want to normalize this ending up with a coding table that looks like:

ID - code
3349 - PCBUS
587 - PCBCA
587 - PCBUS
3350 - PCBCA

etc. Currently the code are store in the client table in a field - each code is separated by commas.

So my thought was to create a CFM page that reads the IDs and codes, and then inserts accordingly into a new code table - I would need to be able to recognize the commas in the code field and loop on those - thuis duplicating the IDs being inserted accordingly.

Any suggestions, ideas on this - let me know.

Thanks

Jeff

You can loop over a list. Just use your comma-delimited codes a list and loop over them, performing an insert for each one.

In pseudo-code:

cfloop query="the big query"
cfset thisListOfCodes = bigQuery.code

cfloop list="#thisListOfCodes#"
cfquery name="insertThisCodeIntoNewTable"
insert into...
/cfquery
/cfloop

/cfloop