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

I really don't like the multi-valued fields. Maybe they did it to make it easier to interface with other multi-valued systems like the old PICK/Unidata system. I bet it's fun upsizing an Access database with heavy use of this new feature to SQL Server.

Answer 2

Necro-post... I think the question should have been revised when the thread first started, but I won't go through the edit process now.

The question is "Multivalued Fields a Good Idea?"

The real question that should have been asked is "Multivalued Fields in RDBMS a Good Idea?"

As others have noted there is an entire MVDBMS model supporting multi-valued fields. I'm an expert in this area and have been working with the model for over 30 years. Of course it's a good idea in my opinion and to others who use the platform every day. And yes, Caché not only has a great multidimensional model itself but it also supports the MVDBMS model. So in this respect, the answer to the question is YES.

But for a RDBMS and specifically MS ACCESS the answer is almost certainly NO because neither the RDBMS model nor that platform inherently support the concept.

The accepted answer is correct, IMO, as it doesn't just answer the question asked, it answers the question that was intended to be asked. But to be meticulous, for the exact question asked, the accepted answer is incorrect.

I believe the real answer is "It's only a good idea if the DBMS platform supports it, YES for MVDBMS and perhaps other NoSQL platforms, NO for RDBMS."