Barbara,
I totally agree with what Jared said. You should, if your customers
have attributes in common (I guess that the address where you send the
invoice is a good common attribute to start with :-)), have a common,
basic 'customers' table (as seen in the sales rep's eye, somebody you
can bring a commission) with a type which tells you what kind of
customer you have - whence where to look for the specific attributes.
This is where your lookups will take place.
But I don't see why you want a trigger. And rather than storing the
primary key from each of the 3 tables, you should use as primary key of
those tables distinct subsets of the primary key of the 'customers' (as
defined above) table.
HTH,
SF
Barbara Baker wrote:
>
> List:
> We're trying to design a CRM app. We believe we need
> 3 tables (Prospect/Customer, Private Party, and
> Agency) because those 3 kinds of (potential) customers
> have different attributes.
>
> The sales rep should know whether they're looking up
> cust, private party, or agency. But what if they
> don't? (They're sales, after all. What if the have a
> hangover?) For performance reasons, we'd prefer not
> to join all 3 tables for a lookup.
>
> I was thinking about 1 cross-reference table with the
> primary key from each of the 3 tables stored in one
> cross-ref table. Any way to keep such a table updated
> other than with a trigger?
>
> Any other ideas about how to do a quick lookup without
> 1 big join?
>
> In case you can't tell, db design is NOT my forte.
> Thanks for any ideas!
>
> Barb
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
