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?

Tenuis

Answer 1

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

Homoio

Answer 2

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.

Amoakuh

Then why did they create them? Clearly they have use in the real world, there is a whole database, Unidata and Universe, that are built around them. – Bobstay

@Noah: a quick google tells me those are based on PICK and not SQL. As I understand it, PICK has the syntax (operators etc) required to query multivalued data, Access/Jet/ACE does not. Not really my field, to be honest (I've used Intersystems Caché but only via its SQL Gateway -- which is excellent -- and not the MUMPS stuff). I'm happiest with 5NF :) – Amoakuh

Answer 3

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

Blynn

Answer 4

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.

Ericaericaceous

Just like 640K and 2 digits to store the year in ;) – Nannette

Answer 5

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

Menfolk

Answer 6

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

Fuqua

Answer 7

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.

Cp

Answer 8

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.

Candracandy

Its only reason for being in ACE is for compatibility with Sharepoint. The data is actually accessible via recordsets and it would be pretty easy to write some VBA to write it out to real N:N tables. Of course, it might very well be that the SSMA for Access 4.2 understands MV fields and upsizes them just fine. If you're interested, I could give it a whack and find out, as I have both A2007 and the SSMA 4.2 installed. – Internalize

@David-W-Fenton: "Its only reason for being in ACE is for compatibility with Sharepoint" -- ...yet the Access Team promote its general use in Access e.g. this doesn't mention SharePoint once: office.microsoft.com/en-us/access-help/… – Amoakuh

Marketing materials very often do not reveal the whole truth. – Internalize

If the tools understand how to handle the MV fields, then upsizing should be fine, and if they don't then scripts could be used, it's just more work. I've seens some difficult conversions from Access to SQL Server in the past and having this feature makes me want to anticipate the added difficulty. I could test it too, but will just wait and see if the need ever arises. Thanks though. – Candracandy