This specific case is regarding lists, and items.
The same item may belong to multiple lists, and each list has many items.
Option A (the "proper" way as I understand it): Make a join table, which has list_ID and item_ID. When I want all the items in a list query for list_ID.
Option B (break the atomicity rule): Make a list table. Primary Key, and either repeating columns or non-atomic columns. As I understand it both of these deviations suffer the exact same drawbacks, which is the inability to inefficiently query on items.
As I understand it, normalizing a database to NF-1 would be making sure each column is atomic, so I should NOT do option B. The reason is that would make querying items hard. E.g "how many of this item got sold" or "how many times this this item is in a list"
I don't think I will ever need that data (is that already a mistake / common pitfall? is there a meme amongst experienced database engineers like "you'll always want to be able to query, and you won't have time to reorganize the database when you scale"? Am I overestimating how long itd take MySQL to scan a join table?
I can't find anything to support this but at the same time I feel that its common sense to accept that non-compliant form if I know that the drawback is not important to the application. But I am not an expert and these rules were written by experts.
See Question&Answers more detail:os