Re: xref table - design consideration

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: sfaroult_at_oriole.com

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: ListGuru_at_fatcity.com (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).