So, I am developing a couple of sites at the moment, and they both are presenting me with a similar problem, from the database side of things. Now, I am no DBA and don't ever profess to be, however, I always set up and design my own DBs. Both sites have existing information that is in a database, so I am looking to integrate it for some functionality moving forward.
Heres the thing:
I have a table filled with pesticide information (you know the things you can spray on crops to control weeds/insect/mold) and each pesticide has a column for whether the product is applicable to a certain crop (so, pesticide X is OK on corn, but bad for green beans) however the architect of the database set up a column for each crop. This table is HUGE must be about 50 columns of crops, and most are empty....
So, in my thought process, I planned on doing a little normalization and streamlining of the data. Take the pesticides and place them in a table separate from what they can be applied on, and then in the OK to apply table, I would just use the ID and then a list to show which crops were OK to apply on.
Now, from my perspective this seems like a good solution, however I think that there is one small holdup. (I am not too concerned about it cause I don't think it will happen, and if it does, its' not my problem, basically, but I am trying to be a good guy). ColdFusion has a ton of ways to access, and manipulate lists, so I can easily check if a crop is in a list for a product etc (although I think that this methodology might also get me in trouble, as I identify what is applicable to a given crop) however, not everything has these features.
From an SQL standpoint, what is the issue (if any) of storing information in lists? or am I better off to set up a table that places the crops in their own cell for each product?
Currently the table would be something like this:
Is this better?
I am curious as to what people think is the best way to handle this data... what is the more efficient methodology?