The next few sections of code, starting at callout I, are situation specific. Each data-transfer job is different, and you'll have to create custom solutions for each situation, but I've included code that addresses a few conditions you might encounter. For example, the code removes commas or tabs—which you'd expect if the original keywords array were a comma-delimited or tab-delimited list—from the ends of words. Or you might run into one or more line-feed carriage-returns after each word or, in an exceptional case, embedded in the middle of a word. In the latter condition, I've opted to simply remove these words from the list because tests I performed outside of this code listing show that words that have embedded line-feed carriage returns have always included comparable words that don't have embedded line-feed, carriage-returns. So, eliminating the problem words doesn't mean you've eliminated the useful entries.
The next section of Web Listing 3 loads the production table. Visual observation of the data will likely reveal duplicate data for nearly every keyword, so the code at callout J checks for and removes duplicate keywords. And you can avoid backward-compatibility problems by loading the original array data into the new table, as the code at callout K shows.
Finally, you're ready to use the code at callout L to transfer data into the associative table IMAGEKEYWORD, which you created to cross-reference images and keywords. Every M:N relationship in the database will be represented by an associative table. Note that I've included the section of code at callout M just in case. This part of the code looks for and removes "blank" keywords. The processing that the code has done so far wouldn't create blank keywords, but if an original record is NULL or contains an empty string, a line-feed, a carriage-return, or a strange character that doesn't print, the target table would still include those anomalies. As you can see, in this example, this section of code returns no rows.
As the code at callout N shows, you must always check for duplicates before loading data into a target table. In this case, a duplicate is the entire row: ID plus keyword. In this example, the code found only one duplicate row and eliminated it in the next step.
As you work through the example, keep checking the row counts. In this example, we started with 33 images from the original source data identified by 97 keywords. As the final check section at callout O shows, we end up with 141 image-key combinations. This difference is within the normal ratio of base to associative table. Finally, when you get to the end of your transfer, don't neglect the cleanup stage. Drop every working table, as the code at callout P shows.
It's one thing to understand how to properly structure tables for keyword lookups, it's harder to transfer data from an old database to a new one. In the process, you'll encounter simple, direct data transfers, you'll pull your hair out over complicated transfers that involve data transformations, and you'll create tables to relate one new data set to another. To get through the process successfully, you have to pay attention to detail, continuously monitor row counts, and visualize what data you need to transfer where.
End of Article
Prev. page
1
2
[3]
next page -->
