supertype to 2 subtypes junctioned then im stuck!

[Mike Krous]

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_CompanyConatact is a junction table to support the N:N relationship for
company contacts*
tbl_CompanyContact 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_CompanyContacts (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 can
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_ContactNumbers that can be related to the individual,
company, and companycontact. where I see the problem is my
tbl_CompanyContacts because it uses a composite key unlike the company or
individual tables so how can I stay normalized and only create one
tbl_ContactNumbers that I can stuff all three sets of numbers in? Hope I
was clear. Any and all advice is always welcome.

Thanks,

Mike Krous

[Allen Browne]

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
tblClientContact 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_********************@comcast.com...

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_CompanyConatact is a junction table to support the N:N relationship for company contacts*
tbl_CompanyContact 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_CompanyContacts (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 can 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_ContactNumbers that can be related to the individual,
company, and companycontact. where I see the problem is my
tbl_CompanyContacts because it uses a composite key unlike the company or
individual tables so how can I stay normalized and only create one
tbl_ContactNumbers that I can stuff all three sets of numbers in? Hope I
was clear. Any and all advice is always welcome.

[Mike Krous]

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" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...

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
tblClientContact 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]

After a second read of your post, I have some questions.

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. 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?
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.) thats what my tbl_CompanyContacts 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.
One possibiltiy is to have
tblClientContact 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. This is exactly what I tried to do, so I made a table

tbl_ContactNumbers
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_CompanyContacts to tbl_ContactNumbers on both fk's
and create another relationship from tbl_Customers to just the IndividualID
of tbl_ContactNumbers. 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" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u... 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
tblClientContact 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_********************@comcast.com...

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_CompanyConatact is a junction table to support the N:N relationship

for

company contacts*
tbl_CompanyContact 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_CompanyContacts (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

can

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_ContactNumbers that can be related to the individual,
company, and companycontact. where I see the problem is my
tbl_CompanyContacts because it uses a composite key unlike the company or individual tables so how can I stay normalized and only create one
tbl_ContactNumbers that I can stuff all three sets of numbers in? Hope I was clear. Any and all advice is always welcome.

[Mike Sherrill]

On Wed, 12 Nov 2003 06:17:02 -0800, "Mike Krous"
<m.krous@nospam_comcast.net> wrote:

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

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
"organization". 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]

> I think you have this backwards.
hmm...sounds a bit extreme for the examples you have provided.

"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. 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".
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.) then they are likely subtypes of something. 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.
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
"organization". That's another clue. "Customer" isn't the supertype. 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

"Parties" is what you're looking for. 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)
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. 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"?
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 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?

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]. 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" <MS*************@compuserve.com> wrote in message
news:jm********************************@4ax.com... On Wed, 12 Nov 2003 06:17:02 -0800, "Mike Krous"
<m.krous@nospam_comcast.net> wrote:

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

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
"organization". 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 Sherrill]

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

[snippage throughout]

I think you have this backwards.hmm...sounds a bit extreme for the examples you have provided.

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.
that is what im modeling, my _customers_. I never said anything about
tracking the seller.
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.
You are correct the buyer may be a individual or
organization but both of those are my _customer_ hence the super-type table
"customer".

[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
"individuals" and "organizations" 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]

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" <MS*************@compuserve.com> wrote in message
news:25********************************@4ax.com...

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

[snippage throughout]

I think you have this backwards.

hmm...sounds a bit extreme for the examples you have provided.

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.

that is what im modeling, my _customers_. I never said anything about
tracking the seller.

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.

You are correct the buyer may be a individual or
organization but both of those are my _customer_ hence the super-type table"customer".

[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
"individuals" and "organizations" 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

[rkc]

"Mike Krous" <m.krous@nospam_comcast.net> wrote in message
news:UO********************@comcast.com...

I think you have this backwards. hmm...sounds a bit extreme for the examples you have provided.

"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.

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".

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.)

then they are likely subtypes of something.

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.

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
"organization". That's another clue. "Customer" isn't the supertype. 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

"Parties" is what you're looking for.

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)

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]

>> 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 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?

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" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:Zm********************@twister.nyroc.rr.com.. .
"Mike Krous" <m.krous@nospam_comcast.net> wrote in message
news:UO********************@comcast.com...

I think you have this backwards.

hmm...sounds a bit extreme for the examples you have provided.

"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.

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".

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.)

then they are likely subtypes of something.

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.

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
"organization". That's another clue. "Customer" isn't the supertype.

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

"Parties" is what you're looking for.

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)

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.

[rkc]

"Mike Krous" <m.krous@nospam_comcast.net> wrote in message
news:OL********************@comcast.com...

guess I was hoping for a response a little closer to my question.

<question>
how do I add a tbl_ContactNumbers that can be related to the individual,
company, and companycontact. where I see the problem is my
tbl_CompanyContacts because it uses a composite key unlike the company or
individual tables so how can I stay normalized and only create one
tbl_ContactNumbers 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]

Thanks for the response. I have a couple of questions.

Add an Autonumber primary key to your CompanyContacts junction table.
Create a unique compound index on IndividualID + CompanyID. 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" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:wR********************@twister.nyroc.rr.com.. .
"Mike Krous" <m.krous@nospam_comcast.net> wrote in message
news:OL********************@comcast.com...

guess I was hoping for a response a little closer to my question.

<question>
how do I add a tbl_ContactNumbers that can be related to the individual,
company, and companycontact. where I see the problem is my
tbl_CompanyContacts because it uses a composite key unlike the company or
individual tables so how can I stay normalized and only create one
tbl_ContactNumbers 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.

[rkc]

"Mike Krous" <m.krous@nospam_comcast.net> wrote in message
news:Ye********************@comcast.com...

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.

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]

On Tue, 18 Nov 2003 20:21:39 -0800, "Mike Krous"
<m.krous@nospam_comcast.net> wrote:

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.

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 "individuals" and "organizations" simply by saying it is.
That's not how logical analysis works.

--
Mike Sherrill
Information Management Systems

[Mike Sherrill]

On Wed, 19 Nov 2003 20:27:37 GMT, "rkc"
<rk*@yabba.dabba.do.rochester.rr.bomb> wrote:

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.

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

[Mike Sherrill]

On Wed, 19 Nov 2003 14:31:00 -0800, "Mike Krous"
<m.krous@nospam_comcast.net> wrote:

Ok, as ive said, yes Parties is technically more correct, I agree.
"Customers", as a supertype of "individuals" and "organizations", is
not merely less correct than "Parties"--it is wrong. At the risk of
repeating myself, "Individuals" is a subtype of "Customers" if and
only if every individual is a customer. Not "every individual I'm
interested in"; not "every individual in this database". _Every_
individual.
I am
building an order entry system and for what we are doing "customer" seemed
just fine.
"It seemed just fine" isn't a sound basis for designing databases, is
it?
(that's how I learned
super/sub typing)
Uh huh.
...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?
Let's think for a minute, shall we?

Conceptually, a minimal "company contact" consists of an organization,
an individual, and (I presume) a phone number. You probably need more
columns than the minimum. A minimal candidate key would include
candiate keys (for you, probably primary keys) from both
[Organizations] and [Individuals], and the column having the phone
number.

Conceptually, a minimal "contact" for an individual consists of an
individual and (again, I presume) a phone number. A minimal candidate
key would include the primary key from [Individuals] and the column
having the phone number. I'm sure you wouldn't even consider storing
these in the same table as the company contacts, because they're
different things.

[snip]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.
He focused on your question, not on your problem.
As far as I could
gather Mike was essentially telling me that I had it "backwards" to change
my table name to "Parties".
You're mistaken. I didn't tell you to change a table's name. I said
you need a different table. "Parties" and "Customers" are both useful
things, but hardly interchangable.

You need a supertype. The supertype you need is "Parties". You might
need a table of customers, too. The customers PK, of course, would be
a foreign key reference to [Parties].
As best as I could read from his post that was
supposed to make my problem magically disappear.

See if your local library can find a copy of Adler and van Doren's
book _How to Read a Book_. In spite of its title, which many find
initially offensive, it's a real eye-opener.

--
Mike Sherrill
Information Management Systems

[Mike Krous]

Ok heres the deal, I built this set of tables and relationships using the
name tbl_Parties. Everything else in my database was the same, I thought to
myself, I dont really care for the name tbl_Parties; I think its vague. So
In my short thought process just prior to this post, I thought "yep"
everyone that gets entered into here is a "customer" so I'll call it
tbl_Customers instead, how clever. In my thought process everyone who was
entered in this structure was only entered because we sold something to
them, or they were the person that facilitated the sale for a company
(CompanyContact). This name seemed fine to me, however it didnt change
anything else about the structure of my design.

When I posted my original question, as to how I would store one table of
phone numbers for the different types of contacts I had you replied about my
name being wrong on tbl_Customers, as far as I can see it, this has nothing
to do with my question. Even if I have an error in my thinking, changing
the name back from tbl_Customers to tbl_Parties does absolutely nothing for
my problem.

I have no problem with that suggestion. But to simply tell me "you have it
backwards" and changing the name, no-matter how phylisophical the concept,
does not apply to my question. I was straight forward from the start that I
thought your post was off base and for you to continue to insist that I am
generally wrong is rude. This is rude because you are completely
disregarding the real issue, my question!

Mike Krous
"Mike Sherrill" <MS*************@compuserve.com> wrote in message
news:rf********************************@4ax.com...

On Tue, 18 Nov 2003 20:21:39 -0800, "Mike Krous"
<m.krous@nospam_comcast.net> wrote:

The only thing I hear from you is squabling over the word I chose to use formy Supertype table and _yes_ it is a supertype table.

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 "individuals" and "organizations" simply by saying it is.
That's not how logical analysis works.

--
Mike Sherrill
Information Management Systems

[Mike Krous]

I am
building an order entry system and for what we are doing "customer" seemedjust fine.
"It seemed just fine" isn't a sound basis for designing databases, is
it?

hmm, does this sound helpful or rude?

(that's how I learned
super/sub typing)

Uh huh.

hmm....rude again.

...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?

Let's think for a minute, shall we?

Well thank you for taking a moment to focus on the question.


Conceptually, a minimal "company contact" consists of an organization,
an individual, and (I presume) a phone number. You probably need more
columns than the minimum. A minimal candidate key would include
candiate keys (for you, probably primary keys) from both
[Organizations] and [Individuals], and the column having the phone
number.
hmm...have you been paying attention? Let's look back at a previous post, my
original post!
<snip>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? <snip>
It says here they may have _multiple_ contact numbers. putting a phone
number as another column with[Organizations] and [Individuals] would not
allow for multiple numbers, doesnt sound like we're quite on track
yet.....maybe do like I posted earlier with a table for numbers. Again, back
to my question, how/do I put them all in one table?
Conceptually, a minimal "contact" for an individual consists of an
individual and (again, I presume) a phone number. and again, not paying attention to my posts.
A minimal candidate
key would include the primary key from [Individuals] and the column
having the phone number. I'm sure you wouldn't even consider storing
these in the same table as the company contacts, because they're
different things.
your correct, I wouldnt store them together either, In fact I wasnt storing
an individual and a company contact in the same table, that wasnt even my
question, my question was wether or not to use one table for contactnumbers
between my "tbl_parties" (ok) and tbl_companycontacts.
[snip]

For
example Allen Browne, he did an excellent job of giving me a suggestion andhe was focused on my problem, it was much appreciated.
He focused on your question, not on your problem.

He did focus on my question, which is my problem. Changing tbl_Customers to
tbl_Parties does not solve my question or my problem, even though im sure it
is a better word.

As far as I could
gather Mike was essentially telling me that I had it "backwards" to changemy table name to "Parties".

You're mistaken. I didn't tell you to change a table's name. I said
you need a different table. "Parties" and "Customers" are both useful
things, but hardly interchangable.

Oh geez, im getting nauseous:

tbl_Customer (super-type table)
CustID - pk
CustType - 'I' or 'C' (to facilitate the sub-type)

tbl_Parties (super-type table)
PartyID - pk
CustType - 'I' or 'C' (to facilitate the sub-type)

now tell me what is so different about those tables? yes I used the wrong
word, but you make it sound like the structure is bad.

As best as I could read from his post that was
supposed to make my problem magically disappear.

See if your local library can find a copy of Adler and van Doren's
book _How to Read a Book_. In spite of its title, which many find
initially offensive, it's a real eye-opener.

I think I would suggest the same book to you, but may I also add the book
"How to Win Friends And Influence People".
Mike Krous
"Mike Sherrill" <MS*************@compuserve.com> wrote in message
news:an********************************@4ax.com... On Wed, 19 Nov 2003 14:31:00 -0800, "Mike Krous"
<m.krous@nospam_comcast.net> wrote:

Ok, as ive said, yes Parties is technically more correct, I agree.

"Customers", as a supertype of "individuals" and "organizations", is
not merely less correct than "Parties"--it is wrong. At the risk of
repeating myself, "Individuals" is a subtype of "Customers" if and
only if every individual is a customer. Not "every individual I'm
interested in"; not "every individual in this database". _Every_
individual.

I am
building an order entry system and for what we are doing "customer" seemedjust fine.

"It seemed just fine" isn't a sound basis for designing databases, is
it?

(that's how I learned
super/sub typing)

Uh huh.

...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?

Let's think for a minute, shall we?

Conceptually, a minimal "company contact" consists of an organization,
an individual, and (I presume) a phone number. You probably need more
columns than the minimum. A minimal candidate key would include
candiate keys (for you, probably primary keys) from both
[Organizations] and [Individuals], and the column having the phone
number.

Conceptually, a minimal "contact" for an individual consists of an
individual and (again, I presume) a phone number. A minimal candidate
key would include the primary key from [Individuals] and the column
having the phone number. I'm sure you wouldn't even consider storing
these in the same table as the company contacts, because they're
different things.

[snip]

For
example Allen Browne, he did an excellent job of giving me a suggestion andhe was focused on my problem, it was much appreciated.

He focused on your question, not on your problem.

As far as I could
gather Mike was essentially telling me that I had it "backwards" to changemy table name to "Parties".

You're mistaken. I didn't tell you to change a table's name. I said
you need a different table. "Parties" and "Customers" are both useful
things, but hardly interchangable.

You need a supertype. The supertype you need is "Parties". You might
need a table of customers, too. The customers PK, of course, would be
a foreign key reference to [Parties].

As best as I could read from his post that was
supposed to make my problem magically disappear.

See if your local library can find a copy of Adler and van Doren's
book _How to Read a Book_. In spite of its title, which many find
initially offensive, it's a real eye-opener.

--
Mike Sherrill
Information Management Systems

[Mike Krous]

> 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. Well to see if I can describe it any better, essentially I have "Parties"
that could be an individual or company, for the companies I want to keep
track of who is a contact for the company but really they are individuals so
I was basically just creating a junction table to hold a key from each table
(companycontacts). The reason I was modeling this way was because I want to
really focus on the company and also wanted to know who within the company.
After that I was trying to put the numbers for both in one table...I am
figuring I need to separate it into two tables....don't know if this
helped...
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.
As far as I can see it, this wouldn't allow me to enforce integrity at the
database level, I guess I'm striving to try and enforce as much integrity as
possible.

Thanks

Mike Krous

"rkc" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:qY********************@twister.nyroc.rr.com.. .
"Mike Krous" <m.krous@nospam_comcast.net> wrote in message
news:Ye********************@comcast.com...

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.
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]

On Tue, 25 Nov 2003 22:53:00 -0800, "Mike Krous"
<m.krous@nospam_comcast.net> wrote:

[snip]

In my short thought process just prior to this post, I thought "yep"
everyone that gets entered into here is a "customer" so I'll call it
tbl_Customers instead, how clever.
Clever is as clever does. By your own description, some individuals
in your database are not customers.

[snip]When I posted my original question, as to how I would store one table of
phone numbers for the different types of contacts I had you replied about my
name being wrong on tbl_Customers, as far as I can see it, this has nothing
to do with my question. Even if I have an error in my thinking, changing
the name back from tbl_Customers to tbl_Parties does absolutely nothing for
my problem.

[snip]

"Parties" is a supertype of individuals and organizations because,
among other things, individuals and organizations participate in many
of the same relationships. One of those relationships is the M:N
relationship between parties and phone numbers.

"Customer" is another of those relationships.

--
Mike Sherrill
Information Management Systems

[rkc]

"Mike Krous" <m.krous@nospam_comcast.net> wrote in message
news:wZ********************@comcast.com...

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.

Well to see if I can describe it any better, essentially I have "Parties"
that could be an individual or company, for the companies I want to keep
track of who is a contact for the company but really they are individuals

so I was basically just creating a junction table to hold a key from each table (companycontacts). The reason I was modeling this way was because I want to really focus on the company and also wanted to know who within the company. After that I was trying to put the numbers for both in one table...I am
figuring I need to separate it into two tables....don't know if this
helped...

You have Companies.
You have Individuals.
A Company can be a Customer.
An Individual can be a Customer.
You have data on a Company solely because it is a Customer.
You have data on an Individual either because they are a Customer
Or because they are a Contact for a Company that is a Customer.
Individuals can be Contacts for more than one Company
And Customers unrelated to a Company, all at the same time.
A Company can have more than one Individual that serves as a
Contact.

Companies have phone numbers that are not related to an Individual.
You call and anyone can pick up the phone. No telling who. Doesn't
matter.
Individuals can have phone numbers unrelated to a Company. You call
the number because you are dealing with them as a Customer.
Individuals can have phone numbers related to a Company. You call
the number because you are dealing with them as a Contact for a
Company that is a Customer.

Is that about right?

When you sell your product/service/whatever, how do you record
who the Customer was?

[Mike Krous]

Perfect summary of what I was trying to say, I guess I wasnt near as elegant
:) Ok, when I sell something to them I track the customer in the Orders
table, because I have used tbl_Parties as my super table, and a 1:1 to
individuals and companies, I have simply CustomerID in the Order table which
relates back to the Parties table which in turn directly relates to either
to individuals or companies depending on which table the key is found in.
Now as for the company contact within a sell (the person facilitating a sale
for a company) I have yet another junction table (OrderCompanyContacts)
related to the Orders table and the CompanyContacts table so I can place in
the table (if one exists) the key of the companycontact and the key of the
order giving me the company contact for that order. In summary, the actual
buyer (company or individual) is the CustomerID field in the order table,
and the contact to a company is only recorded (in the junction table) if the
CustomerID field belongs to a company.

Thanks again,

Mike Krous
"rkc" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:El********************@twister.nyroc.rr.com.. .

"Mike Krous" <m.krous@nospam_comcast.net> wrote in message
news:wZ********************@comcast.com...

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.

Well to see if I can describe it any better, essentially I have "Parties" that could be an individual or company, for the companies I want to keep
track of who is a contact for the company but really they are

individuals so

I was basically just creating a junction table to hold a key from each table

(companycontacts). The reason I was modeling this way was because I

want to

really focus on the company and also wanted to know who within the

company.

After that I was trying to put the numbers for both in one table...I am
figuring I need to separate it into two tables....don't know if this
helped...

You have Companies.
You have Individuals.
A Company can be a Customer.
An Individual can be a Customer.
You have data on a Company solely because it is a Customer.
You have data on an Individual either because they are a Customer
Or because they are a Contact for a Company that is a Customer.
Individuals can be Contacts for more than one Company
And Customers unrelated to a Company, all at the same time.
A Company can have more than one Individual that serves as a
Contact.

Companies have phone numbers that are not related to an Individual.
You call and anyone can pick up the phone. No telling who. Doesn't
matter.
Individuals can have phone numbers unrelated to a Company. You call
the number because you are dealing with them as a Customer.
Individuals can have phone numbers related to a Company. You call
the number because you are dealing with them as a Contact for a
Company that is a Customer.

Is that about right?

When you sell your product/service/whatever, how do you record
who the Customer was?

[Mike Sherrill]

On Tue, 25 Nov 2003 23:22:35 -0800, "Mike Krous"
<m.krous@nospam_comcast.net> wrote:

[snip]

Conceptually, a minimal "company contact" consists of an organization,
an individual, and (I presume) a phone number. You probably need more
columns than the minimum. A minimal candidate key would include
candiate keys (for you, probably primary keys) from both
[Organizations] and [Individuals], and the column having the phone
number.

[snip]It says here they may have _multiple_ contact numbers. putting a phone
number as another column with[Organizations] and [Individuals] would not
allow for multiple numbers

[snip]

Yes, it does.

--
Mike Sherrill
Information Management Systems