> > Why are you storing a list of numbers in a varchar field?
...
> I was storing them as a list so one item could be associated
> with several categories, instead of having to add one for each
> category. All I want is for an ID to be passed, and if that ID
> exists within that column, then display it.
This is not the right approach. You'll want to use an intersection table, as
Mark suggested. The point of using an intersection table is to allow you to
easily construct many-to-many relationships within your database. If you
want to associate one item with many categories, and associate one category
with many items, that's a many-to-many relationship and you'll want a table
between item and category which contains all of the intersections between
items and categories.
I can't overemphasize the importance of correct database design to the
success of application development. If you've come to CF from a
non-programming background, as a lot of people have, you might not have
learned enough about relational databases. CF makes it very easy to learn
how to program, but it doesn't supply the knowledge you need to build
well-designed database applications. The sooner you learn that stuff, the
better your programming life will be. I can tell you this through my own
personal experience. I started programming with CF, and didn't know very
much about database design - and I had lots of problems until I buckled down
and learned how to use a relational database.
To take your situation as an example, there are several potential problems
with storing lists of foreign keys within a field. If you were to delete a
category, you'd have to retrieve the list of categories stored within each
item, modify each list that contains the category to be deleted, then update
those lists. That's a lot of unnecessary work, and doesn't allow the
database to manage itself, as it can with declarative referential integrity
rules. If you simply wanted to list all of the items within a given
category, that would require a similar procedure. On the other hand, if you
simply add an intersection table to your database, you can use that to
perform these operations quickly and efficiently.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
