Re: xref table - design consideration

Barbara,

If you are really modeling the data, you should not be thinking about, or even discussing, tables, SQL, foreign keys, indexes, partitioning or any other physical manifestation.

The point of modeling is to determine if you have accounted for all the data that is needed.

What do Prospect/Customer, Private Party and Agency all have in common? What about them is different?

These may all be sub types of a more generic type, or may not be. I don't know, cuz I know nothing about your business, other than the prospective names of tables that you have supplied.

There was a lot of discussion last week about modeling, so you could find some helpful references in the archives.

HTH Jared

On Mon, 2003-11-24 at 09:44, 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
>
>
> __________________________________
> Do you Yahoo!?
> Free Pop-Up Blocker - Get it now
> http://companion.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Barbara Baker
> INET: barbarabbaker_at_yahoo.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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.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).