Multivalued Fields a Good Idea?

Question

I have recently been introduced to the new Access 2007 feature which is multivalued fields. My initial impression is that it is a bad idea to make use of multiple values in a single field. Traditionally if you wanted to allow for a record to have several values for a field you would create another two tables and link them with foreign keys. This allows for easy querying and ensures that duplicate values reference the same item. Keeping lists in a cell seems like a violation of the purpose of databases.

Are there good uses for these fields which don't make me feel dirty?

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

JUST SAY NO!
if you are learning SQL, learn the right way and normalize your tables. if you know database design do it properly. Not every feature has to be used.

Answer 5

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).

Answer 6

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