For example, let's say I'm building a database that let's a
user create an online store. In my table I'll be storing the
information they provide, such as location, hours, store name, and
so on. In addition, I also want the user to select what type of
merchandise they're selling (from a pre-determined list). Let's say
for the sake of this [obviously fake] example, my list would be:
Now there's two potential ways to store this information...
the first being in a [SQL] varchar field, comma-separated. The
second would be in another table that would include a row featuring
the store_id (from my first table) a fruit_id (from the above list)
for every fruit type selected. Keep in mind my list of fruit will
grow (and possibly shrink) over time, and I'd obviously not want to
update my pages [manually each time, so another table will actually
establish the fruit and my list will be built from a query.
I'm trying to look at all the pros and cons of each storage
method... The user browsing our stores will have the option to
search for stores carrying one of the fruits from the list, or
multiple fruit types. Obviously the separate table storage method
is best, I'm just trying to [before I've even gotten to the
programming part at all] see in my head what the search query would
You don't want to store lists. A second table is the best
solution for every possible reason. A normalized struture will
allow your application to scale better, perform better, be easier
to understand, be easier to debug, be easier to maintain and be
easier to add additional features to.
Storing data as a list in a column is a quick fix which never
pays off in the long term.