Wednesday, January 6, 2010

Regarding Lists and ColdFusion

So, I have a question of sorts I guess, and am looking to see if there is anything wrong with what I plan on doing...

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:

ProductCrops
123corn,green beans,asparagus

Is this better?
ProductCrop
123corn
123green beans

I am curious as to what people think is the best way to handle this data... what is the more efficient methodology?

7 comments:

  1. If you are going to do data normalization then why not go further and set up a crop table with and ID and a pesticide table with an id and then link them in a crop_pesticide (many to many) table to work with?

    After all what happens when your crop gets a corn hybrid that needs less pesticides than normal corn? Will you add it manually to each of the lists or simply create the item in the crop table and then link it to the many to many table?

    ReplyDelete
  2. @Sandra,

    That will make it more efficient as crops are added to the system. I am also trying to plan out a nice UI for the users as well with check boxes etc for what pesticide can be applied to which crop.

    Thanks!

    Rob

    ReplyDelete
  3. Storing lists will cause lots of issues to code around later. Sandra's suggestion on normalization will take a bit more work for you up front but will save lots of work and frustration in other areas.

    ReplyDelete
  4. You do NOT want to create a list of data as in your first example. Each crop should have its own entry. Treating a database table as a tokenized string eliminates most (all?) of the benefits that can be provided by the database engine's indexing system. If you have just pesticide id and crop and index both, then you can do very quick lookups on crop type. If you have to do a select * from crops and split all the text values, you'll be incurring huge data transit times/volumes and processing overhead. Database engines are optimized for the kind of querying you most likely want to do, let them do what they do best.

    ReplyDelete
  5. Many-to-Many is the way to go... Don't wanna code up the data layer? use CF9 + Hibernate! :)

    ReplyDelete
  6. @Henry,

    Unfortunately this project will not see CF9, at least not right away.

    @All,

    Thanks, I was leaning that way anyways, but thought I would 'poll the crowd'

    Cheers!

    ReplyDelete
  7. I certainly agree with the comments about the trouble with lists.

    With apologies for the plug, my (free) DataMgr component set should make this easy without the drawbacks.

    http://www.bryantwebconsulting.com/docs/datamgr/save-many-to-many-relationships.cfm

    ReplyDelete