Multiple fields are a good idea?

Question

I recently introduced the new Access 2007 feature, which is multi-valued fields. My initial impression is that it is a bad idea to use multiple values ​​in the same field. Traditionally, if you want a record to have several values ​​for a field, you would create two more tables and associate them with foreign keys. This makes it easy to query and provides duplicate values ​​for the same item. Saving lists in a cell seems to violate the purpose of the database.

Are there any good benefits for these fields that don't make me feel dirty?

Answer 1

The idea of ​​multi-valued fields was to support the easy creation of report / interface objects, in addition, you can create a form that displays the categories of questions for the problem. Instead of doing some hard work, God forbids joining, it would be easier to store:

Mechanical, electrical

as the value in the field, not

Mechanical Electrical

Personally, I don’t like it and I assume that this type of field was created for non-technical personnel, such as accountants :) (just kidding). No seriously, do not use this unless you are creating a stupid tool that rarely will anyone use, and rarely will anyone ever use.

The right way to handle this is with joins, duplicates, and not many values ​​inside the columns (this is 3nf anyway).

Another reason this was created was to support multiple values ​​within the sharepoint list.

John

Answer 2

Cm:

Multi-valued data types are considered harmful: how dangerous is the data type?

I talked for a long time with Suraj Pujiel, Access Manager program ... and Suray and I sincerely agree that developers do not need to use ambiguous fields. People who understand the database already have a good way to implement many of the many relationships and will not get any benefit from the multi-valued fields.

So, my clear and definite advice, developers should not use multi-valued fields. They have nothing to offer us except possible pain.

Answer 3

Not quite answering this question, but readers may notice that around the idea of MultValued Databases: there is a whole niche industry :

These databases are different from the relational database because they are functions that support the use of attributes having a list of values, and not all attributes having a single value

Since in this case the database engine has extensions to it of the query language in order to take into account the multidimensional nature of its tables (which, I assume, Access probably does not), it is not very comparable with multi-valued fields in Access. But an interesting parallel in any case (for those who have not even heard of MultValued databases before).

Answer 4

A large segment of the Access market is not a developer, but technical users. They may not understand the meaning of normalization, but they may make something work. They just need something simple, and this is better than a free text field, where people type, where you hope that they all type the same thing.

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

Answer 5

multi-valued fields can easily save you the trouble of creating a new table and relationship.

Soda → Types

Why do I need a whole new table to say that Pepsi comes in the usual diet and much more.

I'm sorry that they do not allow us to give columns of multi-valued fields, then they will look like a table, but with much less work

Answer 6

DO NOT SAY NO ONLY!
if you are learning SQL, study the right way and normalize your tables. if you know that database design does it right. Not every function should be used.

Answer 7

I really don't like multi-valued fields. Perhaps they did this in order to facilitate interaction with other multi-valued systems, such as the old PICK / Unidata system. I am sure it’s fun to grow your Access database using this new feature for SQL Server.

Answer 8

Necro-post ... I think that the question should have been revised when the thread first started, but I will not go through the editing process now.

The question "Multiple fields is a good idea?"

The real question that was supposed to be asked is "Are multi-valued fields in RDBMS a good idea?"

As others have already noted, there is a whole MVDBMS model that supports multi-valued fields. I am an expert in this field and have been working with the model for more than 30 years. Of course, this is a good idea, in my opinion, for those who use the platform every day. And yes, Caché has not only an excellent multidimensional model, but also an MVDBMS model. So in this regard, the answer to the question is YES.

But for DBMS and, in particular, MS ACCESS, the answer is almost certainly NO, because neither the DBMS model nor this platform inherently support this concept.

The accepted answer is correct, IMO, because it does not just answer the question asked, it answers the question that should have been asked. But, to be precise, to the exact question, the accepted answer is incorrect.

I believe that the real answer is: "It is a good idea if the DBMS platform supports it, YES for MVDBMS and, possibly, other NoSQL platforms, NO for DBMS."