In yesterday’s article [link] I provided a walkthrough of migrating to a flat SQL Server table. Now we will start digging into data normalization with related tabled. Here we will look at using a One-To-Many table to store binary objects and in the next article we will examine Lookup tables.
Imagine that I designed a SQL Server database with a main “Documents” table and related “Parts” table for storing migrated images, attachments and embedded objects. I wanted a separate table here because there is a one-to-many relationship between documents and images.
The basic design for my database is shown below. In addition to the BinaryData column (type VarBinary) to hold the raw binary data, I added DocID (int) and PartID (string) columns which together constitute a unique primary key. Finally there is a PartName column (string) which stores the friendly display name for the attachments.
Now in my Target Data Definition, I specify my Parts table as a “Normalized Table” with the relationship One-To-Many. If you are lost at this point, go back and review the prior article “Getting Started with Migrating to SQL Server Databases”.
In the Data Fields tab of my Target Data Definition, I need to define two columns for migrating content to. (Unfortunately the “Load From SQL Server Database” button is not smart enough to do a complete job here, so you will have to do much of this manually. If you press that button, make sure you review the following details carefully afterwards.)
First I defined my DocId field for the Parts table and I set the “Link To Primary” property to True. This tells Notes Migrator for SharePoint that the Primary Key of the Primary Table should always be copied to the DocId column of the Parts table. This will happen regardless of whether the Primary Key was set from Notes data or was an auto-generated Identity column.
Next I defined a field of type File. I called it “BinaryData” but I could have called it anything I wanted to. Using the File Data Column, File Key Column, and File Name properties for my File field, I specified the three columns in my database described above for storing information about the migrated binary files. The File Data Column property indicates where the actual binary data should go (the BinaryData column in my case). The File Key Column property indicates where the unique attachment/image/object ID should go (PartID). The File Name Column property indicates where the friendly display name should go (PartName). Note that the File Name Column property is optional and the values written to the indicated column are not guaranteed to be unique.
Finally I set the Is Gating Field property for my File field to True. This is very important, as it controls the normalization of records; if I map an array of 10 values to this field it will cause 10 records to be written to the Parts table.
As shown below, I actually map all the attachments, images, and embedded objects to this field. So if a Notes document had 6 attachments, 2 images and 3 embedded objects, we would get 11 new records in the Parts table.
After running my migration job, we can see that the DocID column values in the Documents table are automatically correlated with the DocID column values in the Parts table, and that it is not a one-to-one relationship.
One final question remains here… If all my images and attachments are stored in a separate table, how does the migrated rich text field (which is now an HTML field in the Documents table) refer to them? I controlled this by using the “Generating Links to SQL Records” options on the Advanced tab of the migration job. Here you can specify how links to images, attachments and even other documents should be formatted in the migrated rich text. This feature is optional, and only makes sense if you have designed a web application to display the images and attachment files in response to a web request.
Using the option shown above, the following attachment link and image reference tags were generated in my rich text document:
<img src=”http://quest.com/parts?server=REMGW0MTL1%5cSHAREPOINT&database=QDemo&table=Parts&id=2382&part=image0.GIF” border=”0″ />