Multivalued Fields a Good Idea?

See:

Multivalued datatypes considered harmful: How dangerous can a data type be?

I had a long talk with Suraj Poozhiyil, the Access Program Manager... both Suraj and I agree wholeheartedly that developers do not need to use multi-valued fields. People who understand databases already have a good way of implementing many to many relationships and will gain no benefit from multi-valued fields.

So, my clear and certain advice to developers is not to use multi-valued fields. They have nothing to offer us except potential pain.

Not really answering the question here, but readers might like to note that there is an entire niche industry around the idea of MultValued Databases:

These databases differ from a relational database in that they have features that support and encourage the use of attributes having a list of values, rather than all attributes having a single value

Since in this case the database engine has extensions to it's query language to accommodate the multi-dimensional nature of it's tables (which I assume Access probably does not) then it's not really comparable to multivalued fields in Access. But an interesting parallel in any case (for anyone who's not previously even heard of MultValued Databases).

The idea of multivalued fields was to support easy creation of report / interface objects, in addition, one can create a form that displays say categories for an issue. Instead of doing some intense work, god forbid joins, it was supposedly simplier to store:

Mechanical, Electrical

as a value in a field rather than

Mechanical Electrical

Personnally I do not like it and assume this type of field was created for non technical personnel like accountants :) (just kidding). No seriously, do not use this unless you are creating a silly tool that rarely anyone will use and rarely anyone will ever have to tap into.

The proper way to handle this is joins, no duplicates, and no multi values inside of columns (this is all 3nf anyhow).

Another reason this was created was to support the multi values inside of a sharepoint list.

Jon

A big segment of the Access market is non-developer, but kind of technical, users. They might not understand the value of normalization, but they can get something to work. They just need something easy and it's better than a free-text field where people type in, where you hope they all type the same thing.

As they learn more, they might start using other tables and foreign keys. But, sometimes, a multi-valued field is good enough.

multivalued fields can easily save you from having to create a new table and relationship.

Soda --> Types

Why do I need a whole new table just to say that Pepsi comes in regular, diet, and more.

I wish they allow us to give multivalued fields columns, then they would be just like a table, but with far less work