Multi value Fields (MVF) Various Topics (2 Viewers)

[MajP]

I created the below linked thread for people to discuss how MVF fields work and if you plan to use them what you need to know.

MVF Technical Discussion and Uses (If you plan to Use Them)

Since there is a lot of discussion now on MVF, I created this thread to provide discussion on understanding how they work, what you can do with them, and how to use them. There is plenty of discussion of the cons of MVF and what you cannot do with them, so no need to rehash that here. But...

www.access-programmers.co.uk

It was meant to be narrowly focused on that topic, but people wanted to discuss other MVF related topics. So this thread can be an open discussion on any issue dealing with MVF.

I do not use them myself for some reasons, but I would not steer away from them in certain cases because I have a good understanding of the capabilities, limitations, and how they work. I understand what I see on the screen is only a representation (like a date field) and not really what is going on. I am more hesitant of recommending them to a novice user because they will not likely what is going on in the background and what are the limitations.

So I will add to this thread a discussion on debunking what I think are myths of MVF.
IMO the discussion on MVF from one of the top writers on Access is misleading in places. I think this does disservice to the rest of the article which provides clear issues with using MVFs.

Microsoft Access Multi-Valued Fields: A Deceptive Feature Best Avoided

Learn why Multi-Valued Fields in Microsoft Access can harm data integrity and performance. Discover best practices for effective database design.

www.devhut.net

1. Myth 1: The author suggests that values are being stored in a single field and thus violates normal data, and leads to other issues. If that was true then yes it would be an issue, but that is not what is going on. There is no "field" holding multiple values, that is only a display. IMO kind of like a date field displaying a date when we know it is storing a number consisting of an integer portion and maybe a decimal portion.

Violation of Database Design Principles​

MVFs violate fundamental database design rules and normalization principles:

• Denormalization: MVFs essentially denormalize data, leading to redundancy and potential inconsistencies.
• First Normal Form Violation: Storing multiple values in a single field violates the First Normal Form (1NF) of database normalization.
• Data Atomicity: MVFs break the principle of data atomicity, making it difficult to work with individual values.

As my linked thread on technical discussion of how MVFs work I do not believe any of the above is true. MVFs are stored in a hidden table, that is normalized each value is its own record so data atomicity is preserved. First Normal Form is not violated, because there is no field holding multiple values. That is just an illusion. It is a display of concatenated data of the records in the hidden and normalized table.

2. Myth 2: The author suggests that most queries are complex.
But I will posit if you built this in a traditional way with a child table or junction table you would have to do the exact same queries. The queries are as easy or easier, but just not as clear to what is really happening.
Joining an MVF by its value field to the lookup table would be identical to joining a table to the junction table to the lookup table. The only difference is you get to see the junction table. With an MVF it is hidden.

Query Limitations​

MVFs severely restrict query capabilities, leading to numerous challenges:

• Complex Queries: Constructing complex queries involving MVFs becomes difficult or impossible.
• Limited SQL Support: Standard SQL operations are not fully supported with MVFs, limiting query flexibility.
• Aggregation Problems: Performing calculations or aggregations on MVF data is cumbersome and error-prone.

Example:

Imagine trying to find all products that have both “red” and “large” attributes stored in an MVF. This simple query becomes unnecessarily complex and may not even be possible without resorting to VBA code.

I think all of that is wrong. If you understand the thing you are looking at is not a field containing multiple values, but a concatenation of the junction/child table, then you can easily query whatever you want. By far the example provided is way wrong and not illustrative.

Imagine trying to find all products that have both “red” and “large” attributes stored in an MVF. This simple query becomes unnecessarily complex and may not even be possible without resorting to VBA code.

In that case I do not even need to understand how an MVF works.

So now just need a query to return all companies in the above query (not all records).
This would be identical to what you would do if you had built your own junction table in a traditional design.

The limited SQL support argument is the same problem. You are likely not querying the MVF field directly. You are either querying the value field which is like querying the junction table or you are linking to the lookup table. See discussion in my linked thread.

The aggregation argument suffer from the same issues. IMO you would aggregate in the exact same way you would do an aggregate query if you could see the junction table. Again see my linked thread.

3. Myth 3 the data is not Normalized and cannot enforce Data validation or integrity.
Again without an example I can not say this is a real issue.

Data Integrity Risks​

MVFs can seriously compromise data integrity:

• Hidden Structures: The hidden nature of the junction table makes it challenging to enforce referential integrity.
• Validation Challenges: Implementing and maintaining data validation rules becomes complex.
• Inconsistent Data: Without proper constraints, inconsistent or duplicate data can easily creep into MVFs

The hidden table is normal and referential integrity can be enforced.
From my linked thread

As you can see there is RI between the Work Categories (MVF) and the lookup table work categories. There is really RI between the hidden junction table and tblCompanies and the junction table and tblWorkCategory.

Is it "hard" to enforce RI because of the hidden table? I guess that is debatable. Seems easy enough
The data validation seems debatable too? You can only pick from the list. You cannot duplicate values. If you need something more complex, Again it would be pretty much that same thing you would have to do if you had built your own junction table.

I am not advocating for use of MVFs and agree with many of the other discussions in that article. But I think this part is misrepresented.

What would benefit this discussion are real examples of these issues and not generalities. If you have examples of queries that cannot be done, inconsistent data, inability to validate data please post. If I could think of good examples I would.