supertype to 2 subtypes junctioned then im stuck!

[Mike Sherrill]

Re: supertype to 2 subtypes junctioned then im stuck!

On Wed, 19 Nov 2003 14:31:00 -0800, "Mike Krous"
<m.krous@nospam _comcast.net> wrote:
[color=blue]
>Ok, as ive said, yes Parties is technically more correct, I agree.[/color]

"Customers" , as a supertype of "individual s" and "organizations" , is
not merely less correct than "Parties"--it is wrong. At the risk of
repeating myself, "Individual s" 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.
[color=blue]
>I am
>building an order entry system and for what we are doing "customer" seemed
>just fine.[/color]

"It seemed just fine" isn't a sound basis for designing databases, is
it?
[color=blue]
>(that's how I learned
>super/sub typing)[/color]

Uh huh.
[color=blue]
>...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?[/color]

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][color=blue]
>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.[/color]

He focused on your question, not on your problem.
[color=blue]
>As far as I could
>gather Mike was essentially telling me that I had it "backwards" to change
>my table name to "Parties".[/color]

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].
[color=blue]
>As best as I could read from his post that was
>supposed to make my problem magically disappear.[/color]

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]

Re: supertype to 2 subtypes junctioned then im stuck!

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" <MSherrillnonon o@compuserve.co m> wrote in message
news:rftrrv4fvj r6h083ada6iunnf po35gatom@4ax.c om...[color=blue]
> On Tue, 18 Nov 2003 20:21:39 -0800, "Mike Krous"
> <m.krous@nospam _comcast.net> wrote:
>[color=green]
> >The only thing I hear from you is squabling over the word I chose to use[/color][/color]
for[color=blue][color=green]
> >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[/color]

[Mike Krous]

Re: supertype to 2 subtypes junctioned then im stuck!

[color=blue][color=green]
> >I am
> >building an order entry system and for what we are doing "customer"[/color][/color]
seemed[color=blue][color=green]
> >just fine.[/color]
>
> "It seemed just fine" isn't a sound basis for designing databases, is
> it?[/color]
hmm, does this sound helpful or rude?
[color=blue][color=green]
> >(that's how I learned
> >super/sub typing)[/color]
>
> Uh huh.[/color]
hmm....rude again.

[color=blue][color=green]
> >...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?[/color]
>
> Let's think for a minute, shall we?[/color]
Well thank you for taking a moment to focus on the question.

[color=blue]
>
> 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.[/color]

hmm...have you been paying attention? Let's look back at a previous post, my
original post!
<snip>[color=blue]
>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?[/color]
<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?
[color=blue]
> Conceptually, a minimal "contact" for an individual consists of an
> individual and (again, I presume) a phone number.[/color]
and again, not paying attention to my posts.
[color=blue]
> 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.[/color]

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_partie s" (ok) and tbl_companycont acts.
[color=blue]
> [snip][color=green]
> >For
> >example Allen Browne, he did an excellent job of giving me a suggestion[/color][/color]
and[color=blue][color=green]
> >he was focused on my problem, it was much appreciated.[/color]
>
> He focused on your question, not on your problem.[/color]
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.

[color=blue][color=green]
> >As far as I could
> >gather Mike was essentially telling me that I had it "backwards" to[/color][/color]
change[color=blue][color=green]
> >my table name to "Parties".[/color]
>
> 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.[/color]
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.

[color=blue][color=green]
> >As best as I could read from his post that was
> >supposed to make my problem magically disappear.[/color]
>
> 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.[/color]

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" <MSherrillnonon o@compuserve.co m> wrote in message
news:antrrvkcsd c407bqscg637u25 j8ucvrkj7@4ax.c om...[color=blue]
> On Wed, 19 Nov 2003 14:31:00 -0800, "Mike Krous"
> <m.krous@nospam _comcast.net> wrote:
>[color=green]
> >Ok, as ive said, yes Parties is technically more correct, I agree.[/color]
>
> "Customers" , as a supertype of "individual s" and "organizations" , is
> not merely less correct than "Parties"--it is wrong. At the risk of
> repeating myself, "Individual s" 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.
>[color=green]
> >I am
> >building an order entry system and for what we are doing "customer"[/color][/color]
seemed[color=blue][color=green]
> >just fine.[/color]
>
> "It seemed just fine" isn't a sound basis for designing databases, is
> it?
>[color=green]
> >(that's how I learned
> >super/sub typing)[/color]
>
> Uh huh.
>[color=green]
> >...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?[/color]
>
> 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][color=green]
> >For
> >example Allen Browne, he did an excellent job of giving me a suggestion[/color][/color]
and[color=blue][color=green]
> >he was focused on my problem, it was much appreciated.[/color]
>
> He focused on your question, not on your problem.
>[color=green]
> >As far as I could
> >gather Mike was essentially telling me that I had it "backwards" to[/color][/color]
change[color=blue][color=green]
> >my table name to "Parties".[/color]
>
> 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].
>[color=green]
> >As best as I could read from his post that was
> >supposed to make my problem magically disappear.[/color]
>
> 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[/color]

[Mike Krous]

Re: supertype to 2 subtypes junctioned then im stuck!

> I'm not sure I would actually model what you appear to be modeling exactly[color=blue]
> the way that is being discussed. It's hard to say because I don't know[/color]
what[color=blue]
> you know about the details.[/color]
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
(companycontact s). 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...
[color=blue]
> 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[/color]
sequence[color=blue]
> of
> autonumbers is that predictable.[/color]

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" <rkc@yabba.dabb a.do.rochester. rr.bomb> wrote in message
news:qY7vb.1177 75$ji3.53773@tw ister.nyroc.rr. com...[color=blue]
>
> "Mike Krous" <m.krous@nospam _comcast.net> wrote in message
> news:YeqdnT20GP U3fSGiRVn-ig@comcast.com. ..
>[color=green]
> > I am still not sure how doing this will allow me to keep one table for[/color]
> phone[color=green]
> > numbers, while enforcing the rules of normalization and[/color]
> database-integrity?[color=green]
> > Isnt that what I want? if the scope of the pk's now overlap, then how[/color][/color]
do[color=blue]
> I[color=green]
> > 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[/color]
what[color=blue]
> 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[/color]
sequence[color=blue]
> of
> autonumbers is that predictable.
>
>[/color]

[Mike Sherrill]

Re: supertype to 2 subtypes junctioned then im stuck!

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

[snip][color=blue]
>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_Customer s instead, how clever.[/color]

Clever is as clever does. By your own description, some individuals
in your database are not customers.

[snip][color=blue]
>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.[/color]
[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]

Re: supertype to 2 subtypes junctioned then im stuck!


"Mike Krous" <m.krous@nospam _comcast.net> wrote in message
news:wZmdnXbome qbyFmi4p2dnA@co mcast.com...[color=blue][color=green]
> > I'm not sure I would actually model what you appear to be modeling[/color][/color]
exactly[color=blue][color=green]
> > the way that is being discussed. It's hard to say because I don't know[/color]
> what[color=green]
> > you know about the details.[/color]
> 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[/color]
so[color=blue]
> I was basically just creating a junction table to hold a key from each[/color]
table[color=blue]
> (companycontact s). The reason I was modeling this way was because I want[/color]
to[color=blue]
> really focus on the company and also wanted to know who within the[/color]
company.[color=blue]
> 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...[/color]

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]

Re: supertype to 2 subtypes junctioned then im stuck!

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 (OrderCompanyCo ntacts)
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" <rkc@yabba.dabb a.do.rochester. rr.bomb> wrote in message
news:Elaxb.1019 47$1N3.80722@tw ister.nyroc.rr. com...[color=blue]
>
> "Mike Krous" <m.krous@nospam _comcast.net> wrote in message
> news:wZmdnXbome qbyFmi4p2dnA@co mcast.com...[color=green][color=darkred]
> > > I'm not sure I would actually model what you appear to be modeling[/color][/color]
> exactly[color=green][color=darkred]
> > > the way that is being discussed. It's hard to say because I don't know[/color]
> > what[color=darkred]
> > > you know about the details.[/color]
> > Well to see if I can describe it any better, essentially I have[/color][/color]
"Parties"[color=blue][color=green]
> > 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[/color][/color]
individuals[color=blue]
> so[color=green]
> > I was basically just creating a junction table to hold a key from each[/color]
> table[color=green]
> > (companycontact s). The reason I was modeling this way was because I[/color][/color]
want[color=blue]
> to[color=green]
> > really focus on the company and also wanted to know who within the[/color]
> company.[color=green]
> > 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...[/color]
>
> 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?
>
>
>[/color]

[Mike Sherrill]

Re: supertype to 2 subtypes junctioned then im stuck!

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

[snip][color=blue][color=green]
>> 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.[/color][/color]
[snip][color=blue]
>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[/color]
[snip]

Yes, it does.

--
Mike Sherrill
Information Management Systems