Path: utzoo!utgpu!watmath!clyde!att!ihlpb!skennedy
From: skennedy@ihlpb.ATT.COM (S. Kennedy)
Newsgroups: comp.databases
Subject: Re: One to Many to One to One relationships in Informix SQL  (LONG!!)
Message-ID: <9220@ihlpb.ATT.COM>
Date: 19 Dec 88 16:41:53 GMT
References: <9176@ihlpb.ATT.COM> <11410004@hpsmtc1.HP.COM>
Reply-To: skennedy@ihlpb.UUCP (S. Kennedy)
Organization: AT&T Bell Laboratories - Naperville, Illinois
Lines: 58

In article <11410004@hpsmtc1.HP.COM> donovan@hpsmtc1.HP.COM (Donovan Hsieh) writes:
>According to your base note which states "Leadsource, location, and type make
>up a unique field". Your sample table A in response 2 has just invalidated
>this assumption  :
>
>TABLE A
>Leadssource        Location        Type      leadnumber                        
>-----------        --------        ----      ----------
>
>  YRHD                B1            R           1347    (1)
>  YRHD                B1            R           1653    (2)
>  YRHD                B1            B            987    (3)
>  CBW                 A0            R            466    (4)
>  CBW                 A0            S           1433    (5)
>  CBW                 A0            S           1944    (6)
>  USER                A0            S            616    (7)
>  USER                A0            S            789    (8)
>  USER                A0            S            987    (9)
>  (etc)
>
>
>The tuples (1) & (2) show that "Leadsource Location Type" does not uniquely
>identify the "leadnumber". This invalidates my initial assumption of FD 1 listed
>in response 1 and your statement as described above. Further more, tuples (4)
>to (9) also invalidate the FD 2 since "Location" does not uniquely identify
>the "Leadsource".
>





But that's my point. In table A, each lead must have a leadsource location type
(LLT), but you may have several leads with the same LLT (You would hope so. If
not, you did a pretty poor job in choosing your advertising! :-) and you
would want to be able to take a count of them and their earned revenue based
on their LLT. That is why the order by at the end is ledasource, location, type,
rundate, leadnumber.


In table B, you would have all the costs of the advertisement LLTs. You may 
have the LLT in twice, but the *rundate* would be different.


What I have been told by several people is that I must come up with a master 
table by either unioning table A and table B together and choosing as outers
all of the tables to this master table; or to create one dynamically and use 
that as my lookup table. (This would be faster as temp tables have no indexes.)



shawn
----

Shawn Kennedy
att!ihlpb!skennedy
AT&T Bell Labs
+1 312 979 2494