Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> 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).Received on Mon Nov 24 2003 - 14:04:28 CST
![]() |
![]() |