That's exactly what I was asking. Thanks, all, for the great responses :)
Eric -----Original Message----- From: Jerry Johnson [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 23, 2002 4:25 PM To: CF-Talk Subject: RE: Making normalized tables from a single flat file. What I think is being asked is how to populate the normalized tables. Use "select distinct into" to populate the lookup tables, Create a lookup field for each field to be normalized alongside the non-normalized field in the non-normalized tables Run a query to populate the fieldID with lookup.ID from lookup table where field=lookup.txt Does that help, at all, or were you asking a completely different question? Jerry Johnson >>> [EMAIL PROTECTED] 05/23/02 04:03PM >>> > I have an Excel spreadsheet that will eventually become a database. Being a > flat file, the column information is very repetitive. I'm hoping that with > a handful of clever queries, I can populate this data into normalized > relational tables. If anyone has any advice or URLs that would help in > making this easier, I would appreciate it. :) Read up on normal forms. Most people recommend going up to third normal form, but it depends on your situation. Here's normal forms as I (somewhat) remember them: First Normal Form: No repetitive columns. (i.e. Don't have columns called color1, color2, color3, etc...) Second & Third Normal Form: Well... I forget what each one is, but basically, everything should relate directly to the primary key. If you have a table called "People" with an primary key of PersonID, then all attributes should relate directly to that a person. For instance, you would want to have columns called EyeColor and NumberOfTeeth because those relate directly to the person. You wouldn't want to have MothersLastName or BrothersMiddleInitial because those do not relate to the person. Instead, you would have a MotherID that links to another row in the table People and perhaps an associative table to hold the brother/sister relationships since they are many-to-many relationships. Can you give us the columns for the flat file and perhaps we can better help you out? Ben Johnson Hostworks, Inc. ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
