supertype to 2 subtypes junctioned then im stuck!

**Allen Browne** · Nov 12 '05, 04:20 PM

Re: supertype to 2 subtypes junctioned then im stuck!

Mike, are individuals and companies both the same kind of entity?
For example, it seems to me that you make sales to individuals and to
companies, etc, etc, so they are both really "clients"?

If that makes sense, would you consider putting both into one table? Use an
IsCorporate yes/no field to distinguish whether this is an individual or
corporate entitiy.

This structure simplies the problem no end, and generates a very flexible
set of options for the relationships. One possibiltiy is to have
tblClientContac t table with *two* foreign keys to tblClient: one for the
company, and one for the individual. If the company is present but the
individual is not, it's a company phone number. If the individual is present
but not the company, it's a personal phone number. If both are present, the
number relates only to the individual in the context of their work at that
company.

There are heaps of other possibilities, including junction tables between
the two copies of the Client table. The junction table can define the role
the individual/company has with another individual/company (e.g. manager of
company, son of individual, or parent company of franchisee, etc.)

The only problem I had with this incredibly flexible set of relations was a
client who wanted to print out a flat-file listing of who was related to
whom. It wasn't worth what he wanted to pay to try to untangle the almost
infinite web of recursive relations that are possible with this structure.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mike Krous" <m.krous@nospam _comcast.net> wrote in message
news:K_ydnZvVV-JooS-i4p2dnA@comcast .com...[color=blue]
> Hello All, I am having some problems modeling a relationship properly and
> could use some advice. My final question is at the bottom of this post
> (everything else is explanation). Basically what I have is a customer to
> our company who can be either an individual or a company, each company may
> have many employees (which are really individuals that work-for *or
> are-related-to* a company), now for the problem: each individual, company
> and company-contact may have multiple contact numbers, how can I stay
> normalized and put all those numbers in one table? Ok I'll explain my
> structures and relationships:
>
> TABLE RELATIONSHIPS:
> =============== =====
> * tbl_Customer is a supertype to tbl_Individual and tbl_Company*
> tbl_Customer 1:1 tbl_Individual
> " " 1:1 tbl_Company
>
> *tbl_CompanyCon atact is a junction table to support the N:N relationship[/color]
for[color=blue]
> company contacts*
> tbl_CompanyCont act M:1 tbl_Individual
> " " M:1 tbl_Company
>
> TABLE STRUCTURES:
> =============== ==
> tbl_Customer (super-type table)
> CustID - pk
> CustType - 'I' or 'C' (to facilitate the sub-type)
>
> tbl_Individual (sub-type table)
> cCustID - pk (same as tbl_customer pk)
> FirstName - string
> LastName - string
>
> tbl_Company (sub-type table)
> cCustID - pk (same as tbl_customer pk)
> CompanyName - string
> Discount - single
>
> tbl_CompanyCont acts (junction table)
> IndividualID - composite pk
> CompanyID - composite pk
> Title - string
>
> Final Structure Note:
> Our customer can be either a company or an individual. If its a company
> there will/can be many contacts. Contacts are also individuals (so they[/color]
can[color=blue]
> be customers either way). Everyone will have contact numbers (and numbers
> will be different when an individual is with a company vs. by themselves)
>
> My Real Question:
> how do I add a tbl_ContactNumb ers that can be related to the individual,
> company, and companycontact. where I see the problem is my
> tbl_CompanyCont acts because it uses a composite key unlike the company or
> individual tables so how can I stay normalized and only create one
> tbl_ContactNumb ers that I can stuff all three sets of numbers in? Hope I
> was clear. Any and all advice is always welcome.[/color]

**Mike Krous** · Nov 12 '05, 04:20 PM

Re: supertype to 2 subtypes junctioned then im stuck!

Allen, thanks for the response. To answer your question as to wheter
individuals and companies are both the same kind of entity, The answer is
no. individuals have a first, middle, last name, a name prefix, name
suffix, and salutation fields to mention just a few, Companies have
CompanyName (one field), tax id, discount, companytype, defaultCarrier,
ect.. What you are describing is how I currently do it, however this setup
has its own neusences (i know thats not how its spelled :) for instance
populating company/individual combo boxes or running reports on name. those
are the ones I think of immediatly, I know the IsCorporate flag will allow
me to use if/thens to properly format the output but it has always seemed
undesirable. What I am really looking to do I suppose is to _Normalize_
this database as much as possible first then go ahead and back track as I
see necessary.

Mike

"Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
news:3fb24367$0 $1747$5a62ac22@ freenews.iinet. net.au...[color=blue]
> Mike, are individuals and companies both the same kind of entity?
> For example, it seems to me that you make sales to individuals and to
> companies, etc, etc, so they are both really "clients"?
>
> If that makes sense, would you consider putting both into one table? Use[/color]
an[color=blue]
> IsCorporate yes/no field to distinguish whether this is an individual or
> corporate entitiy.
>
> This structure simplies the problem no end, and generates a very flexible
> set of options for the relationships. One possibiltiy is to have
> tblClientContac t table with *two* foreign keys to tblClient: one for the
> company, and one for the individual. If the company is present but the
> individual is not, it's a company phone number. If the individual is[/color]
present[color=blue]
> but not the company, it's a personal phone number. If both are present,[/color]
the[color=blue]
> number relates only to the individual in the context of their work at that
> company.
>
> There are heaps of other possibilities, including junction tables between
> the two copies of the Client table. The junction table can define the role
> the individual/company has with another individual/company (e.g. manager[/color]
of[color=blue]
> company, son of individual, or parent company of franchisee, etc.)
>
> The only problem I had with this incredibly flexible set of relations was[/color]
a[color=blue]
> client who wanted to print out a flat-file listing of who was related to
> whom. It wasn't worth what he wanted to pay to try to untangle the almost
> infinite web of recursive relations that are possible with this structure.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.[/color]

**Mike Krous** · Nov 12 '05, 04:20 PM

Re: supertype to 2 subtypes junctioned then im stuck!

After a second read of your post, I have some questions.
[color=blue]
> If that makes sense, would you consider putting both into one table? Use[/color]
an[color=blue]
> IsCorporate yes/no field to distinguish whether this is an individual or
> corporate entitiy.[/color]
Isn't my structure pretty much the same as yours up to the point of phone
numbers. All I did different is subtype the company and individual data.
They are both of the same primary-key-scope of the super-type table
tbl_Customers and I use a field in the tbl_Customers table I=individual
C=Company. I guess my point is, isnt that the same as yours just less empty
fields?
[color=blue]
> There are heaps of other possibilities, including junction tables between
> the two copies of the Client table. The junction table can define the role
> the individual/company has with another individual/company (e.g. manager[/color]
of[color=blue]
> company, son of individual, or parent company of franchisee, etc.)[/color]
thats what my tbl_CompanyCont acts table does, however my problem is its
primary key is a composite key so I have a hard time relating it and other
tables (with single pk's) to another table.
[color=blue]
> One possibiltiy is to have
> tblClientContac t table with *two* foreign keys to tblClient: one for the
> company, and one for the individual. If the company is present but the
> individual is not, it's a company phone number. If the individual is[/color]
present[color=blue]
> but not the company, it's a personal phone number. If both are present,[/color]
the[color=blue]
> number relates only to the individual in the context of their work at that
> company.[/color]
This is exactly what I tried to do, so I made a table

tbl_ContactNumb ers
NumberID - pk (allows for 1:N relationships)
individualID - fk individual table
CompanyID - fk company table
number - string
type - long (uses lookup table)

now how do I enforce referential integrity with this? Or do I not?
I tried to relate the tbl_CompanyCont acts to tbl_ContactNumb ers on both fk's
and create another relationship from tbl_Customers to just the IndividualID
of tbl_ContactNumb ers. however this would not allow me to enter data in the
table becuase It wanted a related record in both tables which could not be
the case if I used only a value in IndividualID and left CompanyID 0 (to
show individual number).

Mike Krous

"Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
news:3fb24367$0 $1747$5a62ac22@ freenews.iinet. net.au...[color=blue]
> Mike, are individuals and companies both the same kind of entity?
> For example, it seems to me that you make sales to individuals and to
> companies, etc, etc, so they are both really "clients"?
>
> If that makes sense, would you consider putting both into one table? Use[/color]
an[color=blue]
> IsCorporate yes/no field to distinguish whether this is an individual or
> corporate entitiy.
>
> This structure simplies the problem no end, and generates a very flexible
> set of options for the relationships. One possibiltiy is to have
> tblClientContac t table with *two* foreign keys to tblClient: one for the
> company, and one for the individual. If the company is present but the
> individual is not, it's a company phone number. If the individual is[/color]
present[color=blue]
> but not the company, it's a personal phone number. If both are present,[/color]
the[color=blue]
> number relates only to the individual in the context of their work at that
> company.
>
> There are heaps of other possibilities, including junction tables between
> the two copies of the Client table. The junction table can define the role
> the individual/company has with another individual/company (e.g. manager[/color]
of[color=blue]
> company, son of individual, or parent company of franchisee, etc.)
>
> The only problem I had with this incredibly flexible set of relations was[/color]
a[color=blue]
> client who wanted to print out a flat-file listing of who was related to
> whom. It wasn't worth what he wanted to pay to try to untangle the almost
> infinite web of recursive relations that are possible with this structure.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Mike Krous" <m.krous@nospam _comcast.net> wrote in message
> news:K_ydnZvVV-JooS-i4p2dnA@comcast .com...[color=green]
> > Hello All, I am having some problems modeling a relationship properly[/color][/color]
and[color=blue][color=green]
> > could use some advice. My final question is at the bottom of this post
> > (everything else is explanation). Basically what I have is a customer[/color][/color]
to[color=blue][color=green]
> > our company who can be either an individual or a company, each company[/color][/color]
may[color=blue][color=green]
> > have many employees (which are really individuals that work-for *or
> > are-related-to* a company), now for the problem: each individual,[/color][/color]
company[color=blue][color=green]
> > and company-contact may have multiple contact numbers, how can I stay
> > normalized and put all those numbers in one table? Ok I'll explain my
> > structures and relationships:
> >
> > TABLE RELATIONSHIPS:
> > =============== =====
> > * tbl_Customer is a supertype to tbl_Individual and tbl_Company*
> > tbl_Customer 1:1 tbl_Individual
> > " " 1:1 tbl_Company
> >
> > *tbl_CompanyCon atact is a junction table to support the N:N relationship[/color]
> for[color=green]
> > company contacts*
> > tbl_CompanyCont act M:1 tbl_Individual
> > " " M:1 tbl_Company
> >
> > TABLE STRUCTURES:
> > =============== ==
> > tbl_Customer (super-type table)
> > CustID - pk
> > CustType - 'I' or 'C' (to facilitate the sub-type)
> >
> > tbl_Individual (sub-type table)
> > cCustID - pk (same as tbl_customer pk)
> > FirstName - string
> > LastName - string
> >
> > tbl_Company (sub-type table)
> > cCustID - pk (same as tbl_customer pk)
> > CompanyName - string
> > Discount - single
> >
> > tbl_CompanyCont acts (junction table)
> > IndividualID - composite pk
> > CompanyID - composite pk
> > Title - string
> >
> > Final Structure Note:
> > Our customer can be either a company or an individual. If its a company
> > there will/can be many contacts. Contacts are also individuals (so they[/color]
> can[color=green]
> > be customers either way). Everyone will have contact numbers (and[/color][/color]
numbers[color=blue][color=green]
> > will be different when an individual is with a company vs. by[/color][/color]
themselves)[color=blue][color=green]
> >
> > My Real Question:
> > how do I add a tbl_ContactNumb ers that can be related to the individual,
> > company, and companycontact. where I see the problem is my
> > tbl_CompanyCont acts because it uses a composite key unlike the company[/color][/color]
or[color=blue][color=green]
> > individual tables so how can I stay normalized and only create one
> > tbl_ContactNumb ers that I can stuff all three sets of numbers in? Hope[/color][/color]
I[color=blue][color=green]
> > was clear. Any and all advice is always welcome.[/color]
>
>[/color]

**Mike Sherrill** · Nov 12 '05, 04:35 PM

Re: supertype to 2 subtypes junctioned then im stuck!

On Wed, 12 Nov 2003 06:17:02 -0800, "Mike Krous"
<m.krous@nospam _comcast.net> wrote:
[color=blue]
>TABLE STRUCTURES:
>============== ===
>tbl_Customer (super-type table)
> CustID - pk
> CustType - 'I' or 'C' (to facilitate the sub-type)
>
>tbl_Individu al (sub-type table)
> cCustID - pk (same as tbl_customer pk)
> FirstName - string
> LastName - string
>
>tbl_Company (sub-type table)
> cCustID - pk (same as tbl_customer pk)
> CompanyName - string
> Discount - single[/color]

I think you have this backwards.

"Customer" usually describes a relationship between a seller and a
buyer. The seller is almost always an organization (even if it's a
sole proprietor with no employees). In most business databases, the
seller isn't recorded at all--it's just understood to be "my company".
The buyer might be either an individual or an organization.

Since individuals and organizations are not entirely the same (duh),
but are not entirely different (they participate in many of the same
relationships), then they are likely subtypes of something. (You were
right about this.)

But "customer" describes a relationship that both individuals and
organizations can participate in. That's one clue. In data modeling,
a supertype is more general or more abstract than its subtypes.
"Customer" isn't more general or abstract than either "individual " or
"organizati on". That's another clue. "Customer" isn't the supertype.

"Parties" is what you're looking for.

Let's say [Parties] is a supertype, containing all the attributes
common to both individuals and organizations. At the very least,
[Parties] will include the name by which the party is known to your
organization, and the type of party it is, say "Ind" for individuals,
"Org" for organizations. [Orgs] contains all the attributes unique to
organizations; [Inds] contains all the attributes unique to
individuals.

Parties generally have addresses, phone numbers, and so on. And
parties in the real world exist independently of wheither they're your
customers. (So you can record information about them as it comes to
hand, not just when they decide to buy something from you.)

I think you should be careful about modeling personnel and contacts.
Many common assumptions are mistaken. (For example, that everyone who
works "for" a company is an employee; some might be independent
contractors or temp agency help.) In general, though, an employer is
an organization, and personnel are individuals. So [Personnel] would
include one foreign key reference to [Orgs] and another foreign key
reference to [Inds].

That enough to get you started?

--
Mike Sherrill
Information Management Systems

**Mike Krous** · Nov 12 '05, 04:37 PM

Re: supertype to 2 subtypes junctioned then im stuck!

> I think you have this backwards.
hmm...sounds a bit extreme for the examples you have provided.
[color=blue]
> "Customer" usually describes a relationship between a seller and a
> buyer. The seller is almost always an organization (even if it's a
> sole proprietor with no employees). In most business databases, the
> seller isn't recorded at all--it's just understood to be "my company".
> The buyer might be either an individual or an organization.[/color]
that is what im modeling, my _customers_. I never said anything about
tracking the seller. You are correct the buyer may be a individual or
organization but both of those are my _customer_ hence the super-type table
"customer".
[color=blue]
> Since individuals and organizations are not entirely the same (duh),
> but are not entirely different (they participate in many of the same
> relationships), then they are likely subtypes of something. (You were
> right about this.)[/color]
[color=blue]
>then they are likely subtypes of something.[/color]
yes of a _Customer_ as I see it, or a "Party" if you want to call it that,
but I believe that title is so vague.
[color=blue]
> But "customer" describes a relationship that both individuals and
> organizations can participate in. That's one clue. In data modeling,
> a supertype is more general or more abstract than its subtypes.
> "Customer" isn't more general or abstract than either "individual " or
> "organizati on". That's another clue. "Customer" isn't the supertype.[/color]
I have to respectfully disagree, a _customer_ with no further description
IS a more generic than the specific type of customer i.e. Individual or
Organization

[color=blue]
> "Parties" is what you're looking for.[/color]
Ok, for the sake of argument lets say I call it "Parties", that didnt change
my structures at all. (or my original problem for that matter)
[color=blue]
> Let's say [Parties] is a supertype, containing all the attributes
> common to both individuals and organizations. At the very least,
> [Parties] will include the name by which the party is known to your
> organization, and the type of party it is, say "Ind" for individuals,
> "Org" for organizations. [Orgs] contains all the attributes unique to
> organizations; [Inds] contains all the attributes unique to
> individuals.[/color]
with the exception of a common name field in the Customer/Parties table
(which I dont see why I would want anyway) this is exactly what I do, so
exactly what are you telling me here? To change my name to "Parties"?
[color=blue]
> Parties generally have addresses, phone numbers, and so on. And
> parties in the real world exist independently of wheither they're your
> customers. (So you can record information about them as it comes to
> hand, not just when they decide to buy something from you.)[/color]
I suppose in a theoretical sense you are correct here, however I wont care
about these people until they are a customer, so I again dont see the big
issue on using the name "Parties", its only a name, it hasnt changed
anything else within my design. Ok, so I call it "Parties," now what?

[color=blue]
> I think you should be careful about modeling personnel and contacts.
> Many common assumptions are mistaken. (For example, that everyone who
> works "for" a company is an employee; some might be independent
> contractors or temp agency help.) In general, though, an employer is
> an organization, and personnel are individuals. So [Personnel] would
> include one foreign key reference to [Orgs] and another foreign key
> reference to [Inds].[/color]
Ok, so the first real suggestion I've heard from you. This would be an
extremely rare case if one organization acted on the behalf of another but I
suppose this could happen. I will take another look at my structures and
see how I could apply this to my situation.

Don't get me wrong, I do appreciate your suggestions, I guess I really have
never cared for generalized, wreckless, negligent comments such as the one
you started your post with. I'd say you have given me nothing more than a
subjective suggestion (which I do appreciate). I wouldn't say I have it
backwards at all, I'd say I have some standing issues with my start to a
normalized structure.

Mike Krous

"Mike Sherrill" <MSherrillnonon o@compuserve.co m> wrote in message
news:jmmirv8pr9 6ccgo2v4q4f60kk gil5r8mpq@4ax.c om...[color=blue]
> On Wed, 12 Nov 2003 06:17:02 -0800, "Mike Krous"
> <m.krous@nospam _comcast.net> wrote:
>[color=green]
> >TABLE STRUCTURES:
> >============== ===
> >tbl_Customer (super-type table)
> > CustID - pk
> > CustType - 'I' or 'C' (to facilitate the sub-type)
> >
> >tbl_Individu al (sub-type table)
> > cCustID - pk (same as tbl_customer pk)
> > FirstName - string
> > LastName - string
> >
> >tbl_Company (sub-type table)
> > cCustID - pk (same as tbl_customer pk)
> > CompanyName - string
> > Discount - single[/color]
>
> I think you have this backwards.
>
> "Customer" usually describes a relationship between a seller and a
> buyer. The seller is almost always an organization (even if it's a
> sole proprietor with no employees). In most business databases, the
> seller isn't recorded at all--it's just understood to be "my company".
> The buyer might be either an individual or an organization.
>
> Since individuals and organizations are not entirely the same (duh),
> but are not entirely different (they participate in many of the same
> relationships), then they are likely subtypes of something. (You were
> right about this.)
>
> But "customer" describes a relationship that both individuals and
> organizations can participate in. That's one clue. In data modeling,
> a supertype is more general or more abstract than its subtypes.
> "Customer" isn't more general or abstract than either "individual " or
> "organizati on". That's another clue. "Customer" isn't the supertype.
>
> "Parties" is what you're looking for.
>
> Let's say [Parties] is a supertype, containing all the attributes
> common to both individuals and organizations. At the very least,
> [Parties] will include the name by which the party is known to your
> organization, and the type of party it is, say "Ind" for individuals,
> "Org" for organizations. [Orgs] contains all the attributes unique to
> organizations; [Inds] contains all the attributes unique to
> individuals.
>
> Parties generally have addresses, phone numbers, and so on. And
> parties in the real world exist independently of wheither they're your
> customers. (So you can record information about them as it comes to
> hand, not just when they decide to buy something from you.)
>
> I think you should be careful about modeling personnel and contacts.
> Many common assumptions are mistaken. (For example, that everyone who
> works "for" a company is an employee; some might be independent
> contractors or temp agency help.) In general, though, an employer is
> an organization, and personnel are individuals. So [Personnel] would
> include one foreign key reference to [Orgs] and another foreign key
> reference to [Inds].
>
> That enough to get you started?
>
> --
> Mike Sherrill
> Information Management Systems[/color]

**Mike Sherrill** · Nov 12 '05, 04:39 PM

Re: supertype to 2 subtypes junctioned then im stuck!

On Tue, 18 Nov 2003 01:05:17 -0800, "Mike Krous"
<m.krous@nospam _comcast.net> wrote:

[snippage throughout][color=blue][color=green]
>> I think you have this backwards.[/color]
>hmm...sounds a bit extreme for the examples you have provided.[/color]

If your thinking is backward--and it *is* backward--then you've simply
put the cart before the horse, and that's easy to fix.
[color=blue]
>that is what im modeling, my _customers_. I never said anything about
>tracking the seller.[/color]

I said essentially the same thing--that most business databases don't
record the seller. That doesn't mean the seller doesn't exist, and it
doesn't mean the seller is irrelevant to logical data analysis.
[color=blue]
>You are correct the buyer may be a individual or
>organization but both of those are my _customer_ hence the super-type table
>"customer".[/color]
[snip remainder]

"Supertype" and "subtype" have a pretty specific meaning in relational
systems. You seem to have misunderstood what "supertype" means.
"Customer" is a supertype of the mutually exclusive subtypes
"individual s" and "organizati ons" if and only if

1) every individual is a customer, and
2) every organization is a customer

While this might be every seller's dream, it's not true in the real
world. Fabian Pascal's book _Practical Issues in Database Management_
has a pretty good treatment of supertype/subtype issues.

--
Mike Sherrill
Information Management Systems

**Mike Krous** · Nov 12 '05, 04:39 PM

Re: supertype to 2 subtypes junctioned then im stuck!

The only thing I hear from you is squabling over the word I chose to use for
my Supertype table and _yes_ it is a supertype table. Other than that I
have heard nothing productive, thanks for trying.

Mike Krous

"Mike Sherrill" <MSherrillnonon o@compuserve.co m> wrote in message
news:25klrvojiv 9ligsrha1gfu51e vi0lhmi1u@4ax.c om...[color=blue]
> On Tue, 18 Nov 2003 01:05:17 -0800, "Mike Krous"
> <m.krous@nospam _comcast.net> wrote:
>
> [snippage throughout][color=green][color=darkred]
> >> I think you have this backwards.[/color]
> >hmm...sounds a bit extreme for the examples you have provided.[/color]
>
> If your thinking is backward--and it *is* backward--then you've simply
> put the cart before the horse, and that's easy to fix.
>[color=green]
> >that is what im modeling, my _customers_. I never said anything about
> >tracking the seller.[/color]
>
> I said essentially the same thing--that most business databases don't
> record the seller. That doesn't mean the seller doesn't exist, and it
> doesn't mean the seller is irrelevant to logical data analysis.
>[color=green]
> >You are correct the buyer may be a individual or
> >organization but both of those are my _customer_ hence the super-type[/color][/color]
table[color=blue][color=green]
> >"customer".[/color]
> [snip remainder]
>
> "Supertype" and "subtype" have a pretty specific meaning in relational
> systems. You seem to have misunderstood what "supertype" means.
> "Customer" is a supertype of the mutually exclusive subtypes
> "individual s" and "organizati ons" if and only if
>
> 1) every individual is a customer, and
> 2) every organization is a customer
>
> While this might be every seller's dream, it's not true in the real
> world. Fabian Pascal's book _Practical Issues in Database Management_
> has a pretty good treatment of supertype/subtype issues.
>
> --
> Mike Sherrill
> Information Management Systems[/color]

**rkc** · Nov 12 '05, 04:40 PM

Re: supertype to 2 subtypes junctioned then im stuck!

"Mike Krous" <m.krous@nospam _comcast.net> wrote in message
news:UOOdnTx01f tpQSSiRVn-tA@comcast.com. ..[color=blue][color=green]
> > I think you have this backwards.[/color]
> hmm...sounds a bit extreme for the examples you have provided.
>[color=green]
> > "Customer" usually describes a relationship between a seller and a
> > buyer. The seller is almost always an organization (even if it's a
> > sole proprietor with no employees). In most business databases, the
> > seller isn't recorded at all--it's just understood to be "my company".
> > The buyer might be either an individual or an organization.[/color]
> that is what im modeling, my _customers_. I never said anything about
> tracking the seller. You are correct the buyer may be a individual or
> organization but both of those are my _customer_ hence the super-type[/color]
table[color=blue]
> "customer".
>[color=green]
> > Since individuals and organizations are not entirely the same (duh),
> > but are not entirely different (they participate in many of the same
> > relationships), then they are likely subtypes of something. (You were
> > right about this.)[/color]
>[color=green]
> >then they are likely subtypes of something.[/color]
> yes of a _Customer_ as I see it, or a "Party" if you want to call it[/color]
that,[color=blue]
> but I believe that title is so vague.
>[color=green]
> > But "customer" describes a relationship that both individuals and
> > organizations can participate in. That's one clue. In data modeling,
> > a supertype is more general or more abstract than its subtypes.
> > "Customer" isn't more general or abstract than either "individual " or
> > "organizati on". That's another clue. "Customer" isn't the supertype.[/color]
> I have to respectfully disagree, a _customer_ with no further description
> IS a more generic than the specific type of customer i.e. Individual or
> Organization
>
>[color=green]
> > "Parties" is what you're looking for.[/color]
> Ok, for the sake of argument lets say I call it "Parties", that didnt[/color]
change[color=blue]
> my structures at all. (or my original problem for that matter)[/color]

If there are individuals that can exist in your system without being a
customer then individual is not a sub-type of customer. In your case
that is an individual that exists solely as a contact for a company.

The company is the customer, not the individual. Individual is not
a sub-type of Customer.

If you're thinking that the individual is the customer acting on behalf
of the company, then the company is not a customer. Company is
not a sub-type of Customer.

Company and Individual can be a customer.

Individual can be a CompanyContact.

**Mike Krous** · Nov 12 '05, 04:41 PM

Re: supertype to 2 subtypes junctioned then im stuck!

>> Parties generally have addresses, phone numbers, and so on. And[color=blue][color=green]
>> parties in the real world exist independently of wheither they're your
>> customers. (So you can record information about them as it comes to
>> hand, not just when they decide to buy something from you.)[/color]
>I suppose in a theoretical sense you are correct here, however I wont care
>about these people until they are a customer, so I again dont see the big
>issue on using the name "Parties", its only a name, it hasnt changed
>anything else within my design. Ok, so I call it "Parties," now what?[/color]

Ok, as ive said, yes Parties is technically more correct, I agree. I am
building an order entry system and for what we are doing "customer" seemed
just fine....Yes "Party" is actually more correct (that's how I learned
super/sub typing)...That however, does nothing for my original question, and
as a result changed nothing more than my table name from tbl_Customers to
tbl_Parties.... ok, now what? I still have the same problem I had to start
with. I have tried to look at this in a more philosophical way, thinking I
was being told to look deeper into the reason behind why I call it what I
call it. That's good and fair, maybe im missing the boat here but as far as
I could see it, it didn't come close to answering my particular question,
guess I was hoping for a response a little closer to my question. For
example Allen Browne, he did an excellent job of giving me a suggestion and
he was focused on my problem, it was much appreciated. As far as I could
gather Mike was essentially telling me that I had it "backwards" to change
my table name to "Parties". As best as I could read from his post that was
supposed to make my problem magically disappear.

Mike

"rkc" <rkc@yabba.dabb a.do.rochester. rr.bomb> wrote in message
news:ZmQub.1241 29$ZC4.24637@tw ister.nyroc.rr. com...[color=blue]
>
> "Mike Krous" <m.krous@nospam _comcast.net> wrote in message
> news:UOOdnTx01f tpQSSiRVn-tA@comcast.com. ..[color=green][color=darkred]
> > > I think you have this backwards.[/color]
> > hmm...sounds a bit extreme for the examples you have provided.
> >[color=darkred]
> > > "Customer" usually describes a relationship between a seller and a
> > > buyer. The seller is almost always an organization (even if it's a
> > > sole proprietor with no employees). In most business databases, the
> > > seller isn't recorded at all--it's just understood to be "my company".
> > > The buyer might be either an individual or an organization.[/color]
> > that is what im modeling, my _customers_. I never said anything about
> > tracking the seller. You are correct the buyer may be a individual or
> > organization but both of those are my _customer_ hence the super-type[/color]
> table[color=green]
> > "customer".
> >[color=darkred]
> > > Since individuals and organizations are not entirely the same (duh),
> > > but are not entirely different (they participate in many of the same
> > > relationships), then they are likely subtypes of something. (You were
> > > right about this.)[/color]
> >[color=darkred]
> > >then they are likely subtypes of something.[/color]
> > yes of a _Customer_ as I see it, or a "Party" if you want to call it[/color]
> that,[color=green]
> > but I believe that title is so vague.
> >[color=darkred]
> > > But "customer" describes a relationship that both individuals and
> > > organizations can participate in. That's one clue. In data modeling,
> > > a supertype is more general or more abstract than its subtypes.
> > > "Customer" isn't more general or abstract than either "individual " or
> > > "organizati on". That's another clue. "Customer" isn't the supertype.[/color]
> > I have to respectfully disagree, a _customer_ with no further[/color][/color]
description[color=blue][color=green]
> > IS a more generic than the specific type of customer i.e. Individual or
> > Organization
> >
> >[color=darkred]
> > > "Parties" is what you're looking for.[/color]
> > Ok, for the sake of argument lets say I call it "Parties", that didnt[/color]
> change[color=green]
> > my structures at all. (or my original problem for that matter)[/color]
>
> If there are individuals that can exist in your system without being a
> customer then individual is not a sub-type of customer. In your case
> that is an individual that exists solely as a contact for a company.
>
> The company is the customer, not the individual. Individual is not
> a sub-type of Customer.
>
> If you're thinking that the individual is the customer acting on behalf
> of the company, then the company is not a customer. Company is
> not a sub-type of Customer.
>
> Company and Individual can be a customer.
>
> Individual can be a CompanyContact.
>
>
>
>
>
>
>[/color]

**rkc** · Nov 12 '05, 04:41 PM

Re: supertype to 2 subtypes junctioned then im stuck!

"Mike Krous" <m.krous@nospam _comcast.net> wrote in message
news:OLKdnVKLRc XNdiaiRVn-sQ@comcast.com. ..
[color=blue]
> guess I was hoping for a response a little closer to my question.[/color]

<question>
how do I add a tbl_ContactNumb ers that can be related to the individual,
company, and companycontact. where I see the problem is my
tbl_CompanyCont acts because it uses a composite key unlike the company or
individual tables so how can I stay normalized and only create one
tbl_ContactNumb ers that I can stuff all three sets of numbers in? Hope I
was clear. Any and all advice is always welcome.
</question>

Well, there's the obvious solution to that, but it's so obvious I just
figured you had already rejected it.

Add an Autonumber primary key to your CompanyContacts junction table.
Create a unique compound index on IndividualID + CompanyID.

I see a lot of Union queries in your future.

**Mike Krous** · Nov 12 '05, 04:42 PM

Re: supertype to 2 subtypes junctioned then im stuck!

Thanks for the response. I have a couple of questions.
[color=blue]
> Add an Autonumber primary key to your CompanyContacts junction table.
> Create a unique compound index on IndividualID + CompanyID.[/color]
As I see it, doing this would essentially bring me back down to one pk and
still keep the other two fields unique via a compound index. But now my
CompanyContact Junction table has a pk of an overlapping scope to my
tbl_Parties (or tbl_Customers as posts have been), for example they could
both have the same primary key value of lets say "5". I guess I dont see
what I should do with that new pk? Or is that simply to facilitate the
ability to create the non-pk compound index instead? Then, still, how do I
enforce referential integrity on my phone-numbers table? Or do I not?

I am still not sure how doing this will allow me to keep one table for phone
numbers, while enforcing the rules of normalization and database-integrity?
Isnt that what I want? if the scope of the pk's now overlap, then how do I
really know which table my record in the phone-number table came from?
Sorry if I'm missing it, need a little more push I guess.

Thanks,

Mike Krous

"rkc" <rkc@yabba.dabb a.do.rochester. rr.bomb> wrote in message
news:wRSub.1248 43$ZC4.45065@tw ister.nyroc.rr. com...[color=blue]
>
> "Mike Krous" <m.krous@nospam _comcast.net> wrote in message
> news:OLKdnVKLRc XNdiaiRVn-sQ@comcast.com. ..
>[color=green]
> > guess I was hoping for a response a little closer to my question.[/color]
>
> <question>
> how do I add a tbl_ContactNumb ers that can be related to the individual,
> company, and companycontact. where I see the problem is my
> tbl_CompanyCont acts because it uses a composite key unlike the company or
> individual tables so how can I stay normalized and only create one
> tbl_ContactNumb ers that I can stuff all three sets of numbers in? Hope I
> was clear. Any and all advice is always welcome.
> </question>
>
> Well, there's the obvious solution to that, but it's so obvious I just
> figured you had already rejected it.
>
> Add an Autonumber primary key to your CompanyContacts junction table.
> Create a unique compound index on IndividualID + CompanyID.
>
> I see a lot of Union queries in your future.
>
>
>
>
>
>
>
>
>
>
>[/color]

**rkc** · Nov 12 '05, 04:43 PM

Re: supertype to 2 subtypes junctioned then im stuck!

"Mike Krous" <m.krous@nospam _comcast.net> wrote in message
news:YeqdnT20GP U3fSGiRVn-ig@comcast.com. ..
[color=blue]
> I am still not sure how doing this will allow me to keep one table for[/color]
phone[color=blue]
> numbers, while enforcing the rules of normalization and[/color]
database-integrity?[color=blue]
> Isnt that what I want? if the scope of the pk's now overlap, then how do[/color]
I[color=blue]
> really know which table my record in the phone-number table came from?
> Sorry if I'm missing it, need a little more push I guess.[/color]

I'm not sure I would actually model what you appear to be modeling exactly
the way that is being discussed. It's hard to say because I don't know what
you know about the details.

Any way, to answer your question, you can initialize the autonumber in the
CompanyContacts table at a level that you are sure your Customer table
will never reach. But, to tell you the truth I am not sure if the sequence
of
autonumbers is that predictable.

**Mike Sherrill** · Nov 12 '05, 04:44 PM

Re: supertype to 2 subtypes junctioned then im stuck!

On Tue, 18 Nov 2003 20:21:39 -0800, "Mike Krous"
<m.krous@nospam _comcast.net> wrote:
[color=blue]
>The only thing I hear from you is squabling over the word I chose to use for
>my Supertype table and _yes_ it is a supertype table.[/color]

You're mistaken.

"Customer" is a supertype of "individual " if and only if every
individual is a customer. But some individuals are not customers.
Even within the narrow context of your database, some individuals are
not customers.

You seem to think that you can convince us that "customers" is a
supertype of "individual s" and "organizati ons" simply by saying it is.
That's not how logical analysis works.

--
Mike Sherrill
Information Management Systems

**Mike Sherrill** · Nov 12 '05, 04:44 PM

Re: supertype to 2 subtypes junctioned then im stuck!

On Wed, 19 Nov 2003 20:27:37 GMT, "rkc"
<rkc@yabba.dabb a.do.rochester. rr.bomb> wrote:
[color=blue]
>If there are individuals that can exist in your system without being a
>customer then individual is not a sub-type of customer. In your case
>that is an individual that exists solely as a contact for a company.[/color]

Supertypes and subtypes are conceptual and logical issues. It
matters only whether there are any individuals anywhere--not just in
the database--who are not customers.

--
Mike Sherrill
Information Management Systems

supertype to 2 subtypes junctioned then im stuck!

supertype to 2 subtypes junctioned then im stuck!

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment