Thanks, Stephane.
Fortunately for us all, I'm not trying to design this
thing. I'm just trying not to sound too dumb when the
designer asks me questions.
The 3 entities have some things in common, but are
different in scope. (An agency never places an ad,
never has a contract, never gets billed. There are
also relationships -- customers can have 1 or more
agencies.)
They DO want to combine customers and prospective
customers, which seems ok.
I like the idea of defining "type". I believe that
will help.
Mladen: I meant was there any other way to keep a
cross-ref table updated besides using a trigger. I
was not trying to use a single trigger to update from
all 3 tables.
Thanks for all of your ideas.
Barb
- Stephane Faroult <sfaroult_at_oriole.com> wrote:
> 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).
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).
Received on Mon Nov 24 2003 - 14:49:39 CST
