I completely agree with Dave. I am currently in the process of redesigning
several important databases that were incorrectly designed in the beginning.
Then tons (tonnes?) of CF code was written to handle the resulting problems
as the database grew. The original designers backed themselves into a
corner. Reporting is a major hassle. Duplicate data is everywhere. They
wrote all kinds of CF stuff to check for duplicates, etc. (which often don't
work correctly). Development time escalated.
At first I was going to look at just fixing the system. After looking at the
CF code and databases I decided to just through it out and start from
scratch. It is the story of "Well, it just started as a simple little ...".
Please, for the love of God Mr. Burns (from one of my favorite Simpson's
episodes), take time to read a good book on RELATIONAL database design.
Keyword RELATIONAL. You will do yourself AND anyone who follows behind you a
really, really huge favor.
-Gary McNeel
> -----Original Message-----
> From: Dave Watts [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, June 27, 2000 11:30 AM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: RE: Selecting one number from a list
>
>
> > > 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.
------------------------------------------------------------------------------
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.
