Is it ever okay to violate first normal form?

Question

I have an Access database that will be required to present data with several one-to-many relationships on one row (e.g., it would list items as "a, b, e, f", and I would have multiple columns like that). I know it's a bad idea to store data that way as well, but considering that I'm allowing the user to filter on several of these columns, I can't think of a better way of dealing with the data than violating first normal form.

As an example: say that I have several journal articles, each of which may report on multiple animals and multiple vegetables. The user can filter on the source name, or they can filter on one or more animals and one or more vegetables. The output should look like

Source name....animals...............vegetables
Source 1.......dogs, cats, birds.....carrots, tomatoes
Source 2.......leopards, birds.......tomatoes, zucchini, apples
Source 3.......cats, goldfish........carrots, cucumbers

Typically you would have a separate table with Source name + animal:

Source name......animal
Source 1.........dog
Source 1.........cats
Source 1.........birds
Source 2.........leopards

etc

and a similar table for vegetables. But considering how the data needs to be presented to the user (a comma-separated list), and how the user filters the data (he may filter to only see sources that include dogs and cats, and sources with carrots and tomatoes), I think it makes sense to store the data as comma separated lists for animals and vegetables. With a comma-separated list, when the user selects multiple vegetables and multiple animals, I can say

WHERE (Vegetables like "*carrots*" and Vegetables like "*tomatoes*") AND (Animals like *dogs*" and Animals like "*cats*")

I can't think of an efficient way to do this same kind of query in Access without using a lot of VBA and multiple queries.

Answer 1

You can always construct a scenario in which violating any rule makes sense, so the answer to the question in your title is Yes.

This is not one of those scenarios, however. The searching and presentation issues you raise are common to most one-to-many relationships (or, at least, to many one-to-many relationships) and if this were a reason to violate first normal form then you wouldn't see a lot of normalized databases.

Construct the database correctly and you won't have to worry about commas, search terms embedded in each other, and slow searches due to the lack of indexes. Write a reusable piece of code to perform the comma-separate roll-ups for you so you don't keep reinventing the wheel.