One of the most important activities to complete when setting out to build your enterprise content management is the planning of your information architecture. It'll have a direct impact on almost every other critical aspect, from usability to management to findability and SharePoint Column Types play a great role in it.
SharePoint 2010 offers a reasonable amount of flexibility in the types of metadata columns it provides for content and process enrichment. But be careful: Without appropriate third party tools, you will likely be permanently bound to your original information architecture design because any type transformation exposes your content to the risk of data loss.
Let's analyze the three[i] types of columns that allow you to reference business data in a structured way: Choice, Lookup and Managed Metadata.
All three columns have the ability to hold values from a curated list, as opposed to free text or numbers edited by anyone. The difference lies in the source of those values, and in the way the data can be used inside your content management system.
SharePoint Column Types 1: Choice
The list of possible values is entered directly inside the column definition, which has an impact on reusability and management. In effect, the choice values will be editable only by users with enough privileges to edit column definition, meaning list-administrators (for columns defined inside the list itself) or site-administrators (for site columns).
Behind-the-scenes, a Choice column's values are stored as concatenated text. The total limit for this is 255 characters, so if you have 50 choices, each one can only be 4-characters long on average (a semicolon is used to separate each value in the concatenation).
Choice columns can be edited as a dropdown list, a group of radio buttons, or a group of check boxes (allowing for multiple selections).
Despite the central definition, each item with a choice column will save its own copy of the selected value. If you edit the column definition afterwards and edit or delete one of the possible choices, the change will not be reflected in the item's value until it is directly edited and re-saved.
Simplest of the three types
Low reusability of choice values
No delegation of edition rights
Not suitable for large multi-value selections
Highest flexibility for visual presentation
Value is saved locally (changes are not automatically propagated)
No support for localization
Can be converted to other simple types (Text, Number, Currency, Date and Time)
When to use them
Small, fixed set of possible values (for example, days of the week. It's unlikely to change soon)
SharePoint Column Types 2: Lookup Columns
Lookups are, as the name implies, columns that reference (lookups) values from a distinct source, in this case another list. They store their value
Multiple lookup columns can point to the same list, but know that the reference list must be in the same site or higher in the same site-collection (although there exists 3rd party products out there that will go beyond this limitation)
The value displayed by lookup columns will be updated automatically if the reference value change. What's more, you can set lookups to deny deletion of reference items if any other items in your site hierarchy are pointing to them.
Visually, lookups appear as a dropdown list.
Lookup columns are a bit more restrictive in their use with other column types. Calculated values cannot use lookup values in their formula, but a lookup can point to a calculated value.
Reusable in same site collection
Most flexible for data type
Not all types of columns can be used as lookup source
Value changes immediately reflected to lookups
Since possible values are stored in a distinct list, permissions can be very granular
Not usable with Metadata Navigation
Type cannot be modified after creation
When to use them
Large number of choices, master-details item relations (such as in a product order list)
SharePoint Column Types 3: Managed Metadata
Managed Metadata is a new type in SharePoint 2010. They are somewhat similar to the previous two types in that they are columns that reference values coming from a remote source. The main strength of Managed Metadata columns is where this remote source is located: In a service application.
What does that mean for end users? Mostly, that the list of values is common to the entire SharePoint farm, and can be managed from one location. Administration can be completely delegated down to a group of terms.
Another interesting feature is that each term can be parent of several sub-terms. So for example, say you have a list of cities, instead of one big list with thousands of values, you can organize them by continent, country and region, allowing for a much more user-friendly selection.
Because of this hierarchy, the value picker for Managed Metadata is more advanced:
Managed Metadata administration is an order of magnitude above the other types. Terms can be merged together or marked as deprecated and made unavailable for new entries. Managed Metadata Columns can be opened (users can add their own values) or closed.
Lastly, Managed Metadata columns fully support multilingual interfaces. Each term can be translated in any number of languages, and SharePoint will display the correct value appropriate for the current user's language.
More complex item picker
Maximal delegation of management rights
Cannot be used in calculated values
Type cannot be modified after creation
When to use:
Columns holding enterprise data, where strict administration is needed
When multilingual sites are a requirement
Let’s finish with all three columns together in one item:
As you can see, there are important differences between each SharePoint Column Type, which can have a profound impact on the way your users will use them (or not use them). When designing your farm, you need to use the right mix of each type and make sure the differences are understood, particularly by content editors. A hasty design will undermine usability and findability. Have you ever found yourself confronted with issues due to these SharePoint Columns Types?
[i] To be really thorough, I should probably mention External Data Columns as another type that supports values from an external source. That last type can point to sources outside of SharePoint, such as third party databases or web services.