Multivalue fields - how bad are they?

[Olga W]

Cursory google research suggests that those in the know shun multi-value fields, because they violate basic db design rules entailing a few problems down the line, and have no advantages over full on junction tables with many-to-many relationships.

I had been considering using them on a text field, to which I would only need to apply select queries or filters. I'd thought that perhaps the problem only really applied to number fields to which you wanted to apply sums.

My own tests suggested that you can run select queries on multi-value fields just fine, and that you can't run filters on them in forms.

BUT ---- When I tested out the alternative junction table model, I found that you can't include the would-be multivalue field in a form except as a subdatasheet, which is a little unweildy, especially if you'd like to include more than one such. And that if you try to include it as a normal field, access ALLOWS this but only includes ONE field, and ONE record, and misleads you by LEAVING OUT any additional values.

Has anyone had futher experience with these different methods and found additonal problems/advantages/solutions?

Thank you so much for your help!

 

[xenou]

I don't know if many will have tested out m-v fields yet - and my guess is experienced db programmers will wish to avoid them for now. However, only time will tell - if we get used to them and learn the tips and tricks they may prove their value.

As far as the awkwardness you find with a subdatasheet, I think that's just a matter of time and experience. You could set the fields to show all the related records too - I think that's a matter of how you format the form ("default view" - single form vs. continuous form). I've found you can make your subforms very inconspicuous by using invisible borders and the same background color as the main form - that is, if you don't want it to "look like" you have a subform. But I wouldn't say it's always easy to work with them - I've burned many long hours tearing my hair out with subforms and still do. I'm pretty sure I still have more to learn myself and I've been using Access for about three years.

If there's any main drawback to the m-v fields that I can think of its the difficulty of working with other systems that might be accessing your data - for instance, an ADO query or from SQL server. Your database might have a hard time communicating with other databases and sharing its data. The database structure itself is less clear by hiding the many-many or one-many relationships in the m-v field. On the other hand, MS uses m-v fields in some other systems (sharepoint services)and so that could turn the argument around. This article suggests not using m-v fields when you might be upsizing to SQL server. I think on the whole I'd go slowly and try it out in less critical parts of your database, then see how it goes.

 

[SydneyGeek]

If you're talking about the multi-value fields in Access 2007, they were created to help Access integrate with SharePoint. Apparently they do create a proper one-to-many structure behind the scenes, but developers can't get to that interface.

And as Xenou mentioned, compatibility with other systems (apart from SharePoint) is likely to be an issue.

Denis