> Please explain how I could normalize this table? Database
> designs is not my forte, but unfortunately, I have the joy
> of learning that hard way.
If you want to build database applications, it's a good idea to spend some
time learning about designing data schemas. I can't overemphasize this. I
see quite a few CF applications, and the biggest problems with those
applications aren't the CF coding itself, but either the database design or
the actual SQL used to query.
> What I have is a list of relationships. For each contact in
> my database, I have a field in the contact table that contains
> the comma delimited list of numbers. These numbers are the
> primary key values for other contacts in the contact table
> that this person has set up a relationship to. Is this not
> the best way to handle this?
It sounds like each contact has one or more relationships with other
contacts in the same table. This is essentially the same as a many-to-many
relationship, except that both sides of the relationship are stored in the
same table. You can create a linking table, which might have two fields:
Contact_Relationship:
Contact_ID - integer
Relationship_Contact_ID - integer
The table should have both fields related to the Contact_ID field within the
Contact table, and should use both fields to define its primary key, and
shouldn't have any identity column
Instead of writing the list of numbers into a field within the contact
table, loop over the list of numbers and insert each one as a relationship
contact ID. For example, let's say that Contact 47 has relationships with
12, 15 and 92. You could then insert each of these relationships into your
Contact_Relationship table:
<!--- Contact_ID = 47, ListOfRelationships = "12,15,92" --->
<CFLOOP INDEX="i" LIST="#ListOfRelationships#">
<CFQUERY NAME="qInsRelationship" ...>
INSERT INTO Contact_Relationship
(Contact_ID,
Relationship_Contact_ID)
VALUES
(#Contact_ID#,
#i#)
</CFQUERY>
</CFLOOP>
This should be enough to get you started.
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.
