many to many problem

[Frank Cutre]

Hi All,

I'm a beginner at Access (just finished reading the first four parts of MS
Office Access 2003 by John Viescas) and I'm designing my first Access
database, a simple (so I thought) Company/Contacts type of database.

My current thinking regarding tables is:
--------------------------
tblCompanies (PK = CompanyID)
tblDivisions (PK = CompanyID,DivisionID)
tblContacts (PK = ContactID)

tblLinkCompaniesDivisions_MM_Contacts (PK = CompanyID,DivisionID,ContactID)

tblAddresses (PK = ToBeDetermined)
tblPhones (PK = ToBeDetermined)
--------------------------

The tblAddresses and/or tblPhones tables can have ManyToMany relationships
with tblCompanies and/or tblDivisions and/or tblContacts tables. One of the
goals for the database is to only have to search one table for addresses or
one table for phone numbers.

I'm struggling with the best way to stay normalized and achieve referential
integrity (my brain is "carmelized" just thinking about it all day today).

1) What's the best way to PK the address and phone tables?
2) What's the best way to link (ManyToMany) the tblAddresses and tblPhones
tables to the tblCompanies, tblDivisions, and tblContacts tables?

Thanx for your time... Frank

 

[tina]

comments inline.

Hi All,

I'm a beginner at Access (just finished reading the first four parts of MS
Office Access 2003 by John Viescas) and I'm designing my first Access
database, a simple (so I thought) Company/Contacts type of database.

My current thinking regarding tables is:

okay. a list of all companies, i take it.

tblDivisions (PK = CompanyID,DivisionID)

what's the purpose of the CompanyID in this table? what do companies have to
do with Divisions "in the real world"?

tblContacts (PK = ContactID)

a list of all contacts (persons?), i take it. are contacts independent of
other real-world entities, such as Companies? or is each Contact connected
with one specific company? or perhaps one specific Division within a
specific company?

tblLinkCompaniesDivisions_MM_Contacts (PK =

CompanyID,DivisionID,ContactID)

i'm completely lost on this one. if this is a linking table, which two
entity tables does it link? using all three foreign key fields as the
primary key in this table indicates to me that one contact may be associated
with multiple divisions and/or companies, and that one division may be
associated with multiple companies and/or contacts - so that it takes the
combination of all three values to achieve a unique key. is all that
correct?

tblAddresses (PK = ToBeDetermined)

is each address related to only one company? or perhaps to only a single
specific division of one company? at any rate, i often use an Autonumber as
the primary key in a table of addresses.

tblPhones (PK = ToBeDetermined)

well presumably each phone number will only call a single Contact - or at
least a single Company. you need to be clear on which it is, or whether it
may be both - a generic "company" number, as well as specific phone numbers
for specific contact persons. again, i often use Autonumber for the primary
key in phone number tables.

i think perhaps you're not clear on the definition of a many-to-many
relationship. it's defined between two entites (not several), and it must
pass *both* the following tests: "one record in table A may be related to
many records in table B, AND one record in table B may be related to many
records in table A."
the standard example of this is: one Customer may order many Products, AND
one Product may be ordered by many Customers. so Customers and Products have
a many-to-many relationship.

One of the
goals for the database is to only have to search one table for addresses or
one table for phone numbers.

I'm struggling with the best way to stay normalized and achieve referential
integrity (my brain is "carmelized" just thinking about it all day today).

1) What's the best way to PK the address and phone tables?
2) What's the best way to link (ManyToMany) the tblAddresses and tblPhones
tables to the tblCompanies, tblDivisions, and tblContacts tables?

referring back to the definition above, i don't think your addresses and
phones have many-to-many relationships with your other tables. for instance:
one Company may have many Addresses BUT presumably each Address belongs to
only one Company. and again, one Company may have many Phones, BUT each
Phone only calls one Company (i hope!). these are one-to-many relationships,
not many-to-many.

I've not read John Viescas' book (though i know that he is held in high
esteem by numerous expert developers here in the newsgroups), so i don't
know how deeply he gets into the subject of relational data modeling. i do
recommend that you pursue more reading and study to get a better grasp of
those principles, before you continue building your tables/relationships. i
can recommend Database Design for Mere Mortals by Michael Hernandez, and the
numerous links at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
beginning with Starting Out and Database Design 101.

and btw, Frank, don't be discouraged. almost no database is ever as simple
as you think it is before you start it. <g> and most people find that
learning and applying the rules of relational data modeling requires a
significant investment in study and practice, to become proficient at it.

hth

 

[Frank Cutre]

Hi Tina,
Thank you very much for your response.

okay. a list of all companies, i take it.
Yes




what's the purpose of the CompanyID in this table?

tblCompanies has a 1ToMany relationship with tblDivisions via the CompanyID
field (column?). DivisionID completes the compound primary key and acts as
the tiebreaker in the tblDivisions table.

what do companies have to do with Divisions "in the real world"?

In "my" real world I have scenarios where divisions can have addresses
and/or phone numbers that need to be grouped by division as a subset of the
company.

a list of all contacts (persons?), i take it.
Yes



are contacts independent of

other real-world entities, such as Companies? or is each Contact

connected with one specific company? or perhaps one specific Division

within a specific company?

A contact can be a single entity on its own or "belong" to any one or
combination of the following:

a single company -or-

a single division of a single company -or- multiple divisions of a single
company -or- a single division of multiple companies -or- Multiple divisions
of mutilple companies

i'm completely lost on this one. if this is a linking table, which two

entity tables does it link?

Companies (and/or company divisions) are on one side of a manyToMany link
table, contacts is on the other side

using all three foreign key fields as the primary key in this table

indicates to me that one contact may be associated with multiple

divisions and/or companies,
Correct



and that one division may be

associated with multiple companies and/or contacts - so that it takes

the combination of all three values to achieve a unique key. is all

that correct?

Almost, one division can have only one company. One company can have many
divisions.

The relationship (I'm new so I'm guessing here) is
[tblCompanies-->1ToMany-->tblDivisions]-->ManyToMany<--tblContacts

is each address related to only one company?

Probaby, but I need to be able to handle when more than one address exists
for one company (i.e. physical, mailing, shipTo and/or billing)

or perhaps to only a single

specific division of one company?

More likely, but I'll need to be able to handle when more than one address
exists for one division (i.e. physical, mailing, shipTo and/or billing)

at any rate, i often use an Autonumber as the primary key in a table

of addresses.

That's what I initially figured on but then how do you protect against
redundant data entry (i.e. many "123 First Street" records due to the
autonumber PK tiebreaker). You could have a hundred contacts at the same
division creating a hundred of the same addresses in tblAddresses unless...
there's more to it.

well presumably each phone number will only call a single Contact - or

at least a single Company. you need to be clear on which it is, or

whether it may be both - a generic "company" number, as well as

specific phone numbers for specific contact persons. again, i often

use Autonumber for the primary key in phone number tables.

Companies, divisions and contacts all may (or may not) have more than one
phone number.

I got a feeling that whatever strategy resolves the tblAddresses issues will
also solve the tblPhones issues.

tables.

i think perhaps you're not clear on the definition of a many-to-many

relationship. it's defined between two entites (not several),

I understand that

and it must

pass *both* the following tests: "one record in table A may be

related to many records in table B, AND one record in table B may be

related to many records in table A."

the standard example of this is: one Customer may order many

Products, AND one Product may be ordered by many Customers. so

Customers and Products have a many-to-many relationship.

In my scenerio one or more of the following is true:

one company (with or without many divisions) may have many contacts, one
contact may have many companies (with or without many divisions), one
contact may be its own entity (no companies or divisions)

today).
tables?


referring back to the definition above, i don't think your addresses

and phones have many-to-many relationships with your other tables. for instance:

one Company may have many Addresses BUT presumably each Address

belongs to only one Company. and again, one Company may have many

Phones, BUT each Phone only calls one Company (i hope!). these are

one-to-many relationships, not many-to-many.

Though not the norm, I do have instances where companies share the same
addresses and phone lines (two examples are; a single small biz owner with
multiple tax ID number entities, and/or multiple small biz owners sharing
the same office/industrial lease space)

I've not read John Viescas' book (though i know that he is held in

high esteem by numerous expert developers here in the newsgroups), so

i don't know how deeply he gets into the subject of relational data

modeling. i do recommend that you pursue more reading and study to get

a better grasp of those principles, before you continue building your

tables/relationships. i can recommend Database Design for Mere

Mortals by Michael Hernandez, and the numerous links at

l beginning with Starting Out and Database Design 101.

I'll check it out, be right back... Cool place Tina, thank you for the web
site pointer. It quickly got added to my list of favs.

and btw, Frank, don't be discouraged. almost no database is ever as simple

as you think it is before you start it. <g> and most people find that

learning and applying the rules of relational data modeling requires a

significant investment in study and practice, to become proficient at it.

Patience? Me? )



If

tblCompanies-->tblLinkCompanyContacts<--tblContacts

Where

tblLinkCompanyContacts is a ManyToMany linking table Can I create

tblLinkCompanyContacts-->tblLinkCompanyContacts_MM_tblAddresses<--tblAdd

tblLinkCompanyContacts-->resses

Where

tblLinkCompanyContacts_MM_tblAddresses is a ManyToMany linking table

Essentially, I'm asking if Access will allow linking between already
existing linking tables? (visualize as a second or third tier linking)



Thank you very very much for your attention to me.



THANX for your time... (^_^)

 

[tina]

i'm happy to keeping working with you to figure this out, Frank (don't let
that deter you from pursuing the additional help i suggested, though). but
you're right in that your entity relationships are somewhat convoluted in
the real world, which makes it more difficult to support them in table
relationships.

i'll post back when i've given your comments some thought, but i'm also
hoping John and/or Lynn will see this post and give us a hand - they each
contribute a lot to this particular newsgroup, and are both better at
unraveling tangled relationships than i am!

tina

Hi Tina,
Thank you very much for your response.

okay. a list of all companies, i take it.
Yes




what's the purpose of the CompanyID in this table?

tblCompanies has a 1ToMany relationship with tblDivisions via the CompanyID
field (column?). DivisionID completes the compound primary key and acts as
the tiebreaker in the tblDivisions table.

what do companies have to do with Divisions "in the real world"?

In "my" real world I have scenarios where divisions can have addresses
and/or phone numbers that need to be grouped by division as a subset of the

company.


a list of all contacts (persons?), i take it.
Yes



are contacts independent of

other real-world entities, such as Companies? or is each Contact

connected with one specific company? or perhaps one specific Division

within a specific company?

A contact can be a single entity on its own or "belong" to any one or
combination of the following:

a single company -or-

a single division of a single company -or- multiple divisions of a single
company -or- a single division of multiple companies -or- Multiple divisions
of mutilple companies

i'm completely lost on this one. if this is a linking table, which two

entity tables does it link?

Companies (and/or company divisions) are on one side of a manyToMany link
table, contacts is on the other side

using all three foreign key fields as the primary key in this table

indicates to me that one contact may be associated with multiple

divisions and/or companies,
Correct



and that one division may be

associated with multiple companies and/or contacts - so that it takes

the combination of all three values to achieve a unique key. is all

that correct?

Almost, one division can have only one company. One company can have many
divisions.

The relationship (I'm new so I'm guessing here) is
[tblCompanies-->1ToMany-->tblDivisions]-->ManyToMany<--tblContacts

is each address related to only one company?

Probaby, but I need to be able to handle when more than one address exists
for one company (i.e. physical, mailing, shipTo and/or billing)

or perhaps to only a single

specific division of one company?

More likely, but I'll need to be able to handle when more than one address
exists for one division (i.e. physical, mailing, shipTo and/or billing)

at any rate, i often use an Autonumber as the primary key in a table

of addresses.

That's what I initially figured on but then how do you protect against
redundant data entry (i.e. many "123 First Street" records due to the
autonumber PK tiebreaker). You could have a hundred contacts at the same
division creating a hundred of the same addresses in tblAddresses unless...
there's more to it.

well presumably each phone number will only call a single Contact - or

at least a single Company. you need to be clear on which it is, or

whether it may be both - a generic "company" number, as well as

specific phone numbers for specific contact persons. again, i often

use Autonumber for the primary key in phone number tables.

Companies, divisions and contacts all may (or may not) have more than one
phone number.

I got a feeling that whatever strategy resolves the tblAddresses issues will
also solve the tblPhones issues.

tables.

i think perhaps you're not clear on the definition of a many-to-many

relationship. it's defined between two entites (not several),

I understand that

and it must

pass *both* the following tests: "one record in table A may be

related to many records in table B, AND one record in table B may be

related to many records in table A."

the standard example of this is: one Customer may order many

Products, AND one Product may be ordered by many Customers. so

Customers and Products have a many-to-many relationship.

In my scenerio one or more of the following is true:

one company (with or without many divisions) may have many contacts, one
contact may have many companies (with or without many divisions), one
contact may be its own entity (no companies or divisions)

today).
tables?


referring back to the definition above, i don't think your addresses

and phones have many-to-many relationships with your other tables. for instance:

one Company may have many Addresses BUT presumably each Address

belongs to only one Company. and again, one Company may have many

Phones, BUT each Phone only calls one Company (i hope!). these are

one-to-many relationships, not many-to-many.

Though not the norm, I do have instances where companies share the same
addresses and phone lines (two examples are; a single small biz owner with
multiple tax ID number entities, and/or multiple small biz owners sharing
the same office/industrial lease space)

I've not read John Viescas' book (though i know that he is held in

high esteem by numerous expert developers here in the newsgroups), so

i don't know how deeply he gets into the subject of relational data

modeling. i do recommend that you pursue more reading and study to get

a better grasp of those principles, before you continue building your

tables/relationships. i can recommend Database Design for Mere

Mortals by Michael Hernandez, and the numerous links at

l beginning with Starting Out and Database Design 101.

I'll check it out, be right back... Cool place Tina, thank you for the web
site pointer. It quickly got added to my list of favs.

and btw, Frank, don't be discouraged. almost no database is ever as

simple

as you think it is before you start it. <g> and most people find that

learning and applying the rules of relational data modeling requires a

significant investment in study and practice, to become proficient at

it.

Patience? Me? )



If

tblCompanies-->tblLinkCompanyContacts<--tblContacts

Where

tblLinkCompanyContacts is a ManyToMany linking table Can I create

tblLinkCompanyContacts-->tblLinkCompanyContacts_MM_tblAddresses<--tblAdd

tblLinkCompanyContacts-->resses

Where

tblLinkCompanyContacts_MM_tblAddresses is a ManyToMany linking table

Essentially, I'm asking if Access will allow linking between already
existing linking tables? (visualize as a second or third tier linking)



Thank you very very much for your attention to me.



THANX for your time... (^_^)

 

[Lynn Trapp]

i'm happy to keeping working with you to figure this out, Frank (don't let

that deter you from pursuing the additional help i suggested, though). but
you're right in that your entity relationships are somewhat convoluted in
the real world, which makes it more difficult to support them in table
relationships.

tina,
After an initial look at this thread, i too agree that Frank's entity
relationships are a bit convoluted. I'm going to start back at the beginning
of the thread and see if I can make some sense of what he's after.

i'll post back when i've given your comments some thought, but i'm also
hoping John and/or Lynn will see this post and give us a hand - they each
contribute a lot to this particular newsgroup, and are both better at
unraveling tangled relationships than i am!

Thanks for the compliment, tina. I'll do what I can, but I think you are, as
always, doing a marvelous job here. But then you already know what I think
on that subject, don't you? ;-)

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Lynn Trapp]

Hi Frank,
tina asked me to take a look at this thread, so I'm going to give it the old
college try. As "a beginner at Access", you have taken on a monumental task
and have made a wise investment in John Viescas's book. John is an excellent
writer. Like tina, I would recommend that you also get a copy of Database
Design for Mere Mortals by Michael Hernandez. It brings database design down
to the level of the average person and will help you in your design phases.
See my other comments inline below.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

Hi All,

My current thinking regarding tables is:

Let me start by asking a simple question -- What real world business problem
is this database supposed to solve? This should always be the first question
you ask yourself when you start designing a database. Sometimes we have a
hard time seeing the forest for the trees. From an initial look at your
design, it appears that you may be trying to overcomplicate things.

I'm making the assumption that one company might have many divisions. If
that's the case, then a more appropriate plan would be:

tblDivisions
DivisionID (PK)
CompanyID (FK)

Then you create a relationship between tblCompanies and tblDivisions and
enforce referential integrity. I don't think you will be able to enforce
referential integrity they way you have it set. The value of a Foreign Key
cannot be understated.

tblContacts (PK = ContactID)

May I ask here what you mean by "contact"? From what you have below, it
looks like you may be using it to refer to someone that is contacted BY
someone in a particular company/division. However, in my mind, a contact
would be someone who is associated with (an employee of) a particular
company/division combination. In that case, you would have a simple
one-to-many relationship built like this:

tblContacts
ContactID (PK)
DivisionID (FK)

Then you create a relationship between tblDivisions and tblContacts. You can
always get back to the contact's company through this relationship. Of
course, I suppose it is possible for there to be a many-to-many relationship
between contacts and divisions, although this would be an exceedingly rare
situation in the business world. Again, I'm using contact to refer to
someone who is an employee of a given division within a given company. If
you are using it as I suggested above, then all bets are off.

tblLinkCompaniesDivisions_MM_Contacts (PK =
CompanyID,DivisionID,ContactID)

tblAddresses (PK = ToBeDetermined)
tblPhones (PK = ToBeDetermined)
--------------------------

The tblAddresses and/or tblPhones tables can have ManyToMany relationships
with tblCompanies and/or tblDivisions and/or tblContacts tables. One of
the
goals for the database is to only have to search one table for addresses
or
one table for phone numbers.

Since a telephone, and an address for that matter, is a single entity, it's
hard to imagine how one telephone could be tied to many companies and/or
divisions. I'm racking my brain to try to figure this one out. Like tina, I
think you really have a one-to-many relationship here. For telephones, I
would create a one-to-many relationship with the Contacts table. Of course,
you might also need to have specific telephones that are tied to the company
and/or division too, but any one telephone will be related to one and only
one of those other entities. So, we are faced with the problem of how to
store ALL telephone numbers (and addresses) in a single table and relate
that table to tblCompanies, tblDivisions, and tblContacts at the same time.
This is not really what I would describe as a many-to-many relationship but
as multiple one-to-many relationships. We will still need a "resolver"
table, but this will require a little more finesse than a typical
many-to-many relationship. Here's one approach you could take, although this
is probably not the only way to solve the problem. Others reading this
thread may have some better suggestions.

tblTelephoneConnections
TelephoneConnectionsID (PK)
CompanyID (FK)
DivisionID (FK)
ContactID (FK)
TelephoneID (FK)

Every record in this table will have, either, a CompanyID/TelephoneID,
DivisionID/TelephoneID, or ContactID/TelephoneID combination but no 2 of
those. I can then get to all Company specific telephones by querying where
DivisionID Is Null and ContactID is null, etc.

 

[Frank Cutre]

Hi Tina,

i'm happy to keeping working with you to figure this out, Frank (don't let
that deter you from pursuing the additional help i suggested, though). but
you're right in that your entity relationships are somewhat convoluted in
the real world, which makes it more difficult to support them in table
relationships.

i'll post back when i've given your comments some thought, but i'm also
hoping John and/or Lynn will see this post and give us a hand - they each
contribute a lot to this particular newsgroup, and are both better at
unraveling tangled relationships than i am!

tina

Thanks again for your time and input... Frank

 

[tina]

I'll do what I can, but I think you are, as

always, doing a marvelous job here.

<bows and smiles, surreptitiously sighs with relief>

 

[Lynn Trapp]

<bows and smiles, surreptitiously sighs with relief>

<nods in response>

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[John Vinson]

i'll post back when i've given your comments some thought, but i'm also
hoping John and/or Lynn will see this post and give us a hand - they each
contribute a lot to this particular newsgroup, and are both better at
unraveling tangled relationships than i am!

I'm in full agreement with Lynn here. I saw this post yesterday and my
first thought was "oh gosh, if *Tina* is having trouble with the table
logic here, it's TOUGH".

I'll try to find some time to review the thread and jump in, but from
what I've seen you're covering things pretty completely!

John W. Vinson[MVP]

 

[Frank Cutre]

Hi Lynn,

Nice to meet you.

It's been years since I've frequented newsgroups so please feel free to
offer any "reply formatting" crtiques as may be needed to help keep this
thread easy to read.

tina asked me to take a look at this thread, so I'm going to give it the old
college try. As "a beginner at Access", you have taken on a monumental task
and have made a wise investment in John Viescas's book. John is an excellent
writer. Like tina, I would recommend that you also get a copy of Database
Design for Mere Mortals by Michael Hernandez. It brings database design down
to the level of the average person and will help you in your design phases.
See my other comments inline below.

I'll check out the book.

Let me start by asking a simple question -- What real world business problem
is this database supposed to solve? This should always be the first question
you ask yourself when you start designing a database.

Contact Management. Names, addresses and phone numbers of individuals,
companies and (as needed) divisions (as a subset of companies).
An individual can be any one or a combination of:
Contact
CompanyContact
CompanyDivisionContact

Sometimes we have a
hard time seeing the forest for the trees. From an initial look at your
design, it appears that you may be trying to overcomplicate things.

Me? )

I'm making the assumption that one company might have many divisions Correct

If that's the case, then a more appropriate plan would be:

tblDivisions
DivisionID (PK)
CompanyID (FK)

Then you create a relationship between tblCompanies and tblDivisions and
enforce referential integrity. I don't think you will be able to enforce
referential integrity they way you have it set. The value of a Foreign Key
cannot be understated.

OK.

What's the difference between

tblDivisions
DivisionID (PK)
CompanyID (FK)

and

tblDivisions
CompanyID (PK,FK)
DivisionID (PK)
?

I'm not a developer, just an end user with some experience at making simple
little DB's to take care of every day biz. Since 1987 my experience is with
Paradox (ver's 1 thru 7), PAL and ObjectPal. Although I read every page in
the first four parts (getting ready to tackle VBA as soon as I get my first
few tables, forms and reports going) of Viescas' Access Inside Out book, I
kinda flew through it and it wouldn't be unlikely if I misunderstood
something.

May I ask here what you mean by "contact"? From what you have below, it
looks like you may be using it to refer to someone that is contacted BY
someone in a particular company/division.

A Contact is a person (human - personal, prospect, customer and/or vendor).
That person may represent:
themself (Contact)
a Company (CompanyContact)
a Company/Division (CompanyDivisionContact)

However, in my mind, a contact
would be someone who is associated with (an employee of) a particular
company/division combination. In that case, you would have a simple
one-to-many relationship built like this:

tblContacts
ContactID (PK)
DivisionID (FK)

In my biz (construction) I can have events with one or more of the
following:
Contact (homeowner/renter client or prospect)
CompanyContact (company client or prospect)
CompanyDivisionContact (company client or prospect requiring CompanyDivision
subset tracking)

Then you create a relationship between tblDivisions and tblContacts. You can
always get back to the contact's company through this relationship.

I understand that.

Of course, I suppose it is possible for there to be a many-to-many relationship
between contacts and divisions, although this would be an exceedingly rare
situation in the business world. Again, I'm using contact to refer to
someone who is an employee of a given division within a given company. If
you are using it as I suggested above, then all bets are off.

I need the ManyToMany linking table between tblCompanyDivision and
tblContact.

I have instances, granted they "were" exceedingly rare at first, but as time
went on (17 years in my biz's case) that "exceedingly rare" list looks more
like an "it's possible" list. Most of my events are with independant
Project Managers (representing multiple companies) and/or small businesses
(both notorious for blossoming into multiple multitasking entities, each
uniquely requiring tracking).

Since a telephone, and an address for that matter, is a single entity, it's
hard to imagine how one telephone could be tied to many companies and/or
divisions. I'm racking my brain to try to figure this one out.

Most people (myself included) usually think of a telephone as a handheld
piece of hardware... a personal item... sitting on my desk or hanging on my
belt... normally not shared with anyone else. Although instant
gratification, cell phones, company extensions and Area Code Overlays are
great testimonials to today's common assumption that everybody has a unique
phone number... we ain't there yet. Call the Jone's house and who
answers?... Bobby, Sue, Mom, Dad or the home office fax (better yet, they
all answer at once). Also, whether to keep employees focused on company (as
opposed to personal) business or not upgrading the phone system (for
whatever reason), there's still plenty of companies out there that host the
outside world using one phone number then "forward" you to the contact.
Trust me, it's not unusual for one PhoneNumber to have many Contacts and one
Contact to have many PhoneNumbers. Of course, that's not to say that a

Like tina, I
think you really have a one-to-many relationship here. For telephones, I
would create a one-to-many relationship with the Contacts table. Of course,
you might also need to have specific telephones that are tied to the company
and/or division too, but any one telephone will be related to one and only
one of those other entities. So, we are faced with the problem of how to
store ALL telephone numbers (and addresses) in a single table and relate
that table to tblCompanies, tblDivisions, and tblContacts at the same time.
This is not really what I would describe as a many-to-many relationship but
as multiple one-to-many relationships. We will still need a "resolver"
table, but this will require a little more finesse than a typical
many-to-many relationship. Here's one approach you could take, although this
is probably not the only way to solve the problem. Others reading this
thread may have some better suggestions.

tblTelephoneConnections
TelephoneConnectionsID (PK)
CompanyID (FK)
DivisionID (FK)
ContactID (FK)
TelephoneID (FK)

Every record in this table will have, either, a CompanyID/TelephoneID,
DivisionID/TelephoneID, or ContactID/TelephoneID combination but no 2 of
those. I can then get to all Company specific telephones by querying where
DivisionID Is Null and ContactID is null, etc.

Oooooh... re-solv-er table... magic. Haven't read about that one yet.
Looks similar to a ManyToMany linking table.

The fact that it starts with a TelephoneConnectionID(PK) field, though,
leads me to believe the possibility exists for redundant record items (ties)
to occur amongst the [CompanyID (FK), DivisionID (FK), ContactID (FK),
TelephoneID (FK)] fields subset, hence the TelephoneConnectionID(PK)
"tiebreaker"... is that correct?

Since tblContact, tblCompanies and tbleDivisions are all capable of
generating many phone numbers there will be a need for TelephoneType,
DefaultForCompany, DefaultForDivision and DefaultForContact fields. Can I
assume those fields should be located into tblTelephoneConnections?

WARNING!...I'm great for asking dumb questions...

If I create the relationship
tblCompanies-->tblLinkCompany_MM_Contacts<--tblContacts
where
tblLinkCompany_MM_Contacts is a ManyToMany linking table
can I then create a new relationship
tblLinkCompany_MM_Contacts-->tblLinkCompanyContacts_MM_tblToBeAnnounced<--tb
lToBeAnnounced
where
tblLinkCompanyContacts_MM_tblToBeAnnounced is a ManyToMany linking table
?
Essentially, I'm asking if Access will allow an existing ManyToMany link
table set to help define (either one side or both sides of) a new ManyToMany
link table set? (visualize as a second or third tier ManyToMany link).

Thanx for your time... Frank

 

[tina]

ok, Frank, having read your additional explanatory detail, and putting it
with the detail in previous posts - i have to say that, though initially i
thought you hadn't really analyzed your "real life" entity relationships and
were making them more convoluted than necessary, i now have a better
understanding of what you're up against. which is not to say that i've
figured out the best way to model the process. <g>

but if you'll stick with this thread for awhile, i'll keep scratching
through rough drafts and see if any light gleams. Lynn and John, i hope
you'll stick with us, too, and help out - at the *very least*, i'll likely
need you to steer me straight when i come with something screwy! <bg>

Hi Lynn,

Nice to meet you.

It's been years since I've frequented newsgroups so please feel free to
offer any "reply formatting" crtiques as may be needed to help keep this
thread easy to read.

tina asked me to take a look at this thread, so I'm going to give it the old
college try. As "a beginner at Access", you have taken on a monumental task
and have made a wise investment in John Viescas's book. John is an excellent
writer. Like tina, I would recommend that you also get a copy of Database
Design for Mere Mortals by Michael Hernandez. It brings database design down
to the level of the average person and will help you in your design phases.
See my other comments inline below.

I'll check out the book.

Let me start by asking a simple question -- What real world business problem
is this database supposed to solve? This should always be the first question
you ask yourself when you start designing a database.

Contact Management. Names, addresses and phone numbers of individuals,
companies and (as needed) divisions (as a subset of companies).
An individual can be any one or a combination of:
Contact
CompanyContact
CompanyDivisionContact

Sometimes we have a
hard time seeing the forest for the trees. From an initial look at your
design, it appears that you may be trying to overcomplicate things.

Me? )

I'm making the assumption that one company might have many divisions Correct

If that's the case, then a more appropriate plan would be:

tblDivisions
DivisionID (PK)
CompanyID (FK)

Then you create a relationship between tblCompanies and tblDivisions and
enforce referential integrity. I don't think you will be able to enforce
referential integrity they way you have it set. The value of a Foreign Key
cannot be understated.

OK.

What's the difference between

tblDivisions
DivisionID (PK)
CompanyID (FK)

and

tblDivisions
CompanyID (PK,FK)
DivisionID (PK)
?

I'm not a developer, just an end user with some experience at making simple
little DB's to take care of every day biz. Since 1987 my experience is with
Paradox (ver's 1 thru 7), PAL and ObjectPal. Although I read every page in
the first four parts (getting ready to tackle VBA as soon as I get my first
few tables, forms and reports going) of Viescas' Access Inside Out book, I
kinda flew through it and it wouldn't be unlikely if I misunderstood
something.

May I ask here what you mean by "contact"? From what you have below, it
looks like you may be using it to refer to someone that is contacted BY
someone in a particular company/division.

A Contact is a person (human - personal, prospect, customer and/or vendor).
That person may represent:
themself (Contact)
a Company (CompanyContact)
a Company/Division (CompanyDivisionContact)

However, in my mind, a contact
would be someone who is associated with (an employee of) a particular
company/division combination. In that case, you would have a simple
one-to-many relationship built like this:

tblContacts
ContactID (PK)
DivisionID (FK)

In my biz (construction) I can have events with one or more of the
following:
Contact (homeowner/renter client or prospect)
CompanyContact (company client or prospect)
CompanyDivisionContact (company client or prospect requiring CompanyDivision
subset tracking)

Then you create a relationship between tblDivisions and tblContacts. You can
always get back to the contact's company through this relationship.

I understand that.

Of course, I suppose it is possible for there to be a many-to-many relationship
between contacts and divisions, although this would be an exceedingly rare
situation in the business world. Again, I'm using contact to refer to
someone who is an employee of a given division within a given company. If
you are using it as I suggested above, then all bets are off.

I need the ManyToMany linking table between tblCompanyDivision and
tblContact.

I have instances, granted they "were" exceedingly rare at first, but as time
went on (17 years in my biz's case) that "exceedingly rare" list looks more
like an "it's possible" list. Most of my events are with independant
Project Managers (representing multiple companies) and/or small businesses
(both notorious for blossoming into multiple multitasking entities, each
uniquely requiring tracking).

Since a telephone, and an address for that matter, is a single entity, it's
hard to imagine how one telephone could be tied to many companies and/or
divisions. I'm racking my brain to try to figure this one out.

Most people (myself included) usually think of a telephone as a handheld
piece of hardware... a personal item... sitting on my desk or hanging on my
belt... normally not shared with anyone else. Although instant
gratification, cell phones, company extensions and Area Code Overlays are
great testimonials to today's common assumption that everybody has a unique
phone number... we ain't there yet. Call the Jone's house and who
answers?... Bobby, Sue, Mom, Dad or the home office fax (better yet, they
all answer at once). Also, whether to keep employees focused on company (as
opposed to personal) business or not upgrading the phone system (for
whatever reason), there's still plenty of companies out there that host the
outside world using one phone number then "forward" you to the contact.
Trust me, it's not unusual for one PhoneNumber to have many Contacts and one
Contact to have many PhoneNumbers. Of course, that's not to say that a

Like tina, I
think you really have a one-to-many relationship here. For telephones, I
would create a one-to-many relationship with the Contacts table. Of course,
you might also need to have specific telephones that are tied to the company
and/or division too, but any one telephone will be related to one and only
one of those other entities. So, we are faced with the problem of how to
store ALL telephone numbers (and addresses) in a single table and relate
that table to tblCompanies, tblDivisions, and tblContacts at the same time.
This is not really what I would describe as a many-to-many relationship but
as multiple one-to-many relationships. We will still need a "resolver"
table, but this will require a little more finesse than a typical
many-to-many relationship. Here's one approach you could take, although this
is probably not the only way to solve the problem. Others reading this
thread may have some better suggestions.

tblTelephoneConnections
TelephoneConnectionsID (PK)
CompanyID (FK)
DivisionID (FK)
ContactID (FK)
TelephoneID (FK)

Every record in this table will have, either, a CompanyID/TelephoneID,
DivisionID/TelephoneID, or ContactID/TelephoneID combination but no 2 of
those. I can then get to all Company specific telephones by querying where
DivisionID Is Null and ContactID is null, etc.

Oooooh... re-solv-er table... magic. Haven't read about that one yet.
Looks similar to a ManyToMany linking table.

The fact that it starts with a TelephoneConnectionID(PK) field, though,
leads me to believe the possibility exists for redundant record items (ties)
to occur amongst the [CompanyID (FK), DivisionID (FK), ContactID (FK),
TelephoneID (FK)] fields subset, hence the TelephoneConnectionID(PK)
"tiebreaker"... is that correct?

Since tblContact, tblCompanies and tbleDivisions are all capable of
generating many phone numbers there will be a need for TelephoneType,
DefaultForCompany, DefaultForDivision and DefaultForContact fields. Can I
assume those fields should be located into tblTelephoneConnections?

WARNING!...I'm great for asking dumb questions...

If I create the relationship
tblCompanies-->tblLinkCompany_MM_Contacts<--tblContacts
where
tblLinkCompany_MM_Contacts is a ManyToMany linking table
can I then create a new relationship
tblLinkCompany_MM_Contacts-->tblLinkCompanyContacts_MM_tblToBeAnnounced<--tb
lToBeAnnounced
where
tblLinkCompanyContacts_MM_tblToBeAnnounced is a ManyToMany linking table
?
Essentially, I'm asking if Access will allow an existing ManyToMany link
table set to help define (either one side or both sides of) a new ManyToMany
link table set? (visualize as a second or third tier ManyToMany link).

Thanx for your time... Frank

 

[tina]

thanks, John <puts ice pack on swelling head>
this is a toughie, really. i'm usually proficient at the standard business
relationships, they're pretty well in the box. but Frank's entities *do*
have convoluted connections in real life, and i'm not so good at "outside
the box" relationship modeling - not enough experience, probably (aren't you
guys a little older than me? <g, d & r>)

seriously, i'll be grateful for any help you and/or Lynn can offer - even if
it's just critiqueing my hopefully-not-too-lame design suggestions.

 

[Frank Cutre]

Hi Tina,

ok, Frank, having read your additional explanatory detail, and putting it
with the detail in previous posts - i have to say that, though initially i
thought you hadn't really analyzed your "real life" entity relationships and
were making them more convoluted than necessary, i now have a better
understanding of what you're up against.

You understand?... I'm not making my real life more convoluted than it
really is? )

If "we" were to step back, and look at the bigger picture, all I'm really
trying to do is keep ALL phone numbers in ONE TABLE... period. No matter
what generates phone number... Contact, Company, CompanyDivision. Need a
phone number?... then start with the entity and follow the relationship IN
to the phone table. Want to know what entities are using a phone number?...
then start with the phone number and follow the relationship OUT of the
phone table. Given that, the phone table should be a tightly packed
collection of phone numbers with no redundant line items (any phone number
appears once and only once in the phone table). Under that strategy I
assumed there'd be some sort of "utility" type of table in the equation
fanning into and out of the phone table (Viescas' "M2M linking" table and
Lynn's "resolver" table are utility types of tables in my mind).

The last time I played with databases, phone numbers and addresses was the
late 80's (when Borland was king <g>). Now I have a chance to redesign out
the bad and design in some new. I was kinda hoping database features and
strategies evolved for the better (simpler) over the past 15 years (that's
why I dropped a fishin' line here in this newsgroup) but what do I know...
I'm just the drywall sub )

One thing's for sure, holding hands with considerate, giving, knowledgeable
people like yourself and Lynn sure makes the learning curve much more
enjoyable. As long as you guys keep spilling it out I'll keep sponging it
up.

THANX for your time... (^_^)

 

[Lynn Trapp]

Hi Frank,
Nice to meet you too. I gather you came from the world of Paradox. That's
where I got my feet wet in database programming too. As for formatting,
there is no hard and fast rule on that. Some people post at the top. Others
post at the bottom. I usually cut and paste sections and post my responses
below them.

What's the difference between

tblDivisions
DivisionID (PK)
CompanyID (FK)

and

tblDivisions
CompanyID (PK,FK)
DivisionID (PK)
?

In practical terms there is not a lot of difference, but there is little, if
any, reason to have CompanyID as part of the Primary Key for tblDivisions.
It's purpose in tblDivisions is not to uniquely identify a record in that
table (since it will appear multiple times), but to provide a means of
relating records in tblDivisions with records in tblCompanies. It fits the
definition of a Foreign Key very well. If you are concerned about
duplicates, you can always add a compound Unique Index on DivisionId and
CompanyID.

That person may represent:
themself (Contact)
a Company (CompanyContact)
a Company/Division (CompanyDivisionContact)

One of the things you might consider doing here is to consider an
individual that represents himself as a company. For example, where I work,
I am an employee AND a vendor. My employee ID is actually stored in our
Vendor's table. That allows the company to pay my expenses through our AP
system. There is a field for vendor_type in our Vendor's table. I'm of type
"Employee", whereas normal vendors are of type "Vendor". You could add a
field for company_type to tblCompanies and then they become a company
contact. Just a thought that might help simplify some things for you.

In my biz (construction) I can have events with one or more of the
following:
Contact (homeowner/renter client or prospect)
CompanyContact (company client or prospect)

I'm assuming you do home construction for individuals, like me and John and
tina, and also do commercial construction for companies. Is this correct?
Or, is the Company someone who deals with the homeowners separate from you?
Or is it a combination of the two? I'm still thinking making the individual
a part of tblCompanies might simplify things here.

I have instances, granted they "were" exceedingly rare at first, but as
time
went on (17 years in my biz's case) that "exceedingly rare" list looks
more
like an "it's possible" list. Most of my events are with independant
Project Managers (representing multiple companies) and/or small businesses
(both notorious for blossoming into multiple multitasking entities, each
uniquely requiring tracking).

Well, that's an interesting scenario that I've never encountered. In that
case you could use the multi-one-to-many resolver table that I described to
make the connections.

Trust me, it's not unusual for one PhoneNumber to have many Contacts and
one
Contact to have many PhoneNumbers. Of course, that's not to say that a
ManyToMany linking table is the solution (<wink> I just read ahead).

I understand that, but I still think you will find that a single phone
number is tied to some single entity somewhere.

The fact that it starts with a TelephoneConnectionID(PK) field, though,
leads me to believe the possibility exists for redundant record items
(ties)
to occur amongst the [CompanyID (FK), DivisionID (FK), ContactID (FK),
TelephoneID (FK)] fields subset, hence the TelephoneConnectionID(PK)
"tiebreaker"... is that correct?

You could put it that way.

Since tblContact, tblCompanies and tbleDivisions are all capable of
generating many phone numbers there will be a need for TelephoneType,
DefaultForCompany, DefaultForDivision and DefaultForContact fields. Can I
assume those fields should be located into tblTelephoneConnections?

That would be one way to do it.

Essentially, I'm asking if Access will allow an existing ManyToMany link
table set to help define (either one side or both sides of) a new
ManyToMany
link table set? (visualize as a second or third tier ManyToMany link).

It can probably be done but the more levels you add to something like that
the more difficult it is to maintain AND, most importantly, to built data
entry forms that will allow for correct update and insertion of records.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Frank Cutre]

Hi Lynn,

Nice to meet you too. I gather you came from the world of Paradox. That's
where I got my feet wet in database programming too.

I miss the "hey" days of SoCalPALS and PdoxInformant. There use to be some
fun groups that met monthly around SoCal. I stopped upgrading when Borland
passed the ball to Corel.

In practical terms there is not a lot of difference, but there is little, if
any, reason to have CompanyID as part of the Primary Key for tblDivisions.

Since Access has to maintain an index anyways (in either of the above
examples) I figured why not just have the table's primary composite keep all
companies and their related divisions together (saving Access from one
additional sort maintenance). One good reason I can think of to put
DivisionID(PK) first in line is to make use of Access' autoNumber property
but then... that raises another question for me.

Pdox had the autoIncrement data type also but (for lock control reasons) it
was not favored in a networking environment (a requirement for my app) so
the standard procedure was to generate primary keys through a utility table
on the side (where locks could be controlled). Does Access' autoNumber data
type "really" have networking lock issues secured (no two users anywhere on
the the network can ever generate the same primary key at the same time...
period... no ifs, ands, or buts) or is it still wiser to generate primary
keys thru a utility table on the side?

It's purpose in tblDivisions is not to uniquely identify a record in that
table (since it will appear multiple times), but to provide a means of
relating records in tblDivisions with records in tblCompanies. It fits the
definition of a Foreign Key very well. If you are concerned about
duplicates, you can always add a compound Unique Index on DivisionId and
CompanyID.

Please refresh my memory (I'm sorry, I really did read every single page of
the first four parts of Viescas' book... but I read it really fast). Are
you saying that (even though during the table build process I designate BOTH
CompanyID and DivisionID to be PKs) there's an additional step I need to
take to let Access know this is in fact a compound PK (Access doesn't assume
it)?

One of the things you might consider doing here is to consider an
individual that represents himself as a company. For example, where I work,
I am an employee AND a vendor. My employee ID is actually stored in our
Vendor's table. That allows the company to pay my expenses through our AP
system. There is a field for vendor_type in our Vendor's table. I'm of type
"Employee", whereas normal vendors are of type "Vendor". You could add a
field for company_type to tblCompanies and then they become a company
contact. Just a thought that might help simplify some things for you.

That is how I designed my Contacts table back in the 80's. The following is
a partial of that table structure:
tblID
IDnum (smallInt, PK)
LastName (alphanumeric80) <-- or company name
FirstName (alphanumeric50) <-- or company division
MidName (alphanumeric20)
Sex (alphanumeric1) <-- M=Male, F=Female, B=Mr & Mrs, C=company

Sex didn't account for an IDnum to be BOTH Female and Company but that's
something I'll take care of this time around. Besides, back then, (if
Sex=C) I didn't care if an individual was Male or Female... uhhhh no reading
into that last statement please )

Anyhoo, since I'm designing from scratch again anyways, I'm was planning on
tightening up my table definitions. Are you saying my old approach is still
good? I did have some form and report design issues with that table
structure (they weren't insurmountable... more of a pain in the butt). One
thing's for sure (with the old structure), my ID reports will print out as:

Adams, Linda
Baron, Joe
Company1, Division1
Company1, Division2
Company1, Division3
Dickens, Beth

instead of

Adams, Linda
Baron, Joe
Company1
.........Division1
.........Division2
.........Division3
Dickens, Beth

I'm assuming you do home construction for individuals, like me and John and
tina, and also do commercial construction for companies. Is this correct?
Or, is the Company someone who deals with the homeowners separate from you?
Or is it a combination of the two?

All of the above PLUS there's another company type (Construction Management)
that holds a position in a communications relationship path BUT NOT IN a
financial/accounting relationship path. No biggie, I'm able to handle that
one on my own... famous last words )

I'm still thinking making the individual
a part of tblCompanies might simplify things here.

One thing I'm nervous about with this approach is... I have a feeling
(sometime, somewhere to perform some database related job WHILE keeping
everything tight and normalized)... I'll have to reference a table to
itself... and (currently) I'm having a hugh problem grasping the
"constructive use" of that concept (with databases). Ya see, I'm also a
musician. A table referencing itself (my interpretation) is the same as me
standing in front of my amplifier holding my guitar. The sound comes out
the speakers and goes back into the guitar's pickup... a continual,
regenerating loop ("feedback" in sound reinforcement terms). I understand
(when managed properly) feedback can sound as sweet as Stairway To Heaven
but, improperly, it sounds like the Sex Pistols. With regards to databases
I want my application to sing like Stairway... not honk like the Sex
Pistols. I'm insecure with self references... I do have an ego though )

Well, that's an interesting scenario that I've never encountered. In that
case you could use the multi-one-to-many resolver table that I described to
make the connections.

Oh yeah... the re-solv-er (wasn't that the name of a Beatles album?)
table. I had a question about that (I forgot to ask the last time).

Ref your quote:
====================
tblTelephoneConnections
TelephoneConnectionsID (PK)
CompanyID (FK)
DivisionID (FK)
ContactID (FK)
TelephoneID (FK)

Every record in this table will have, either, a CompanyID/TelephoneID,
DivisionID/TelephoneID, or ContactID/TelephoneID combination but no 2 of
those. I can then get to all Company specific telephones by querying where
DivisionID Is Null and ContactID is null, etc.
====================
The "but no 2 of those" part threw me. I know it's probably some
rediculously simple concept but could you explain that again... slower...
for me... please... (easy on me, it's been years since I've been working
"routinely" with basic database procedures, methods and rules)

I understand that, but I still think you will find that a single phone
number is tied to some single entity somewhere.

Correct... multiple times... got it!

It can probably be done but the more levels you add to something like that
the more difficult it is to maintain AND, most importantly, to built data
entry forms that will allow for correct update and insertion of records.

Understood... smart planning... don't reinvent the wheel... be careful.


OK... Lynn-ette... here's a couple of new ones:

1)======================
Please feel free NOT to spend time on this one (if you're not familiar with
Viescas' Access 2003 Inside Out). I sent the following to MS Press'
support several times but I don't think they like to answer support
questions:

Reference MSPress Access2003 Inside Out

I'm building my first project from scratch using the table structures on the
book's companion CD as a guide. In design view, I'm not quite understanding
what the LENGTH property of a column(field) does. The closest explanation
of LENGTH (I found) is on page 615 (first paragraph); "restrict the length
of data entered to no more than"... (the number of characters entered for
this property I'm assuming). But that is not how the field appears to
function in tableview (ref: tlkpContactEventTypes on the book's companion
CD).

Ref: tlkpContactEventTypes
Column = ContactEventFollowUpDays
Data Type = smalint
Length = 2

Length = 2 leads me to believe that 99 is the largest integer allowed in the
field but tableview shows entries for 360 (I was also able to modify a rec
and post a value of 4500). What am I misunderstanding?

2)======================
Pdox (I'm sure remember) generated individual files for tables, forms,
reports, primary indexes, sencondary indexes, validity checks, etc. I had
about 30 or 40 tables tied to my old design application (generating hundreds
of support files). Table sizes ranged from 2 (smallest) to 150,000
(largest) records with most averaging about 1500 to 3000 records. All this
stuff I use to keep in one subdirectory. I see Access not only handles all
database support in ONE .mdb file (thats cool) but also has the capability
to have MULTIPLE .mdb files interact with each other. What I need to know
is (given the table sizes of my application and the fact that this app isn't
going out to market), do I put everything into one .mdb or more than one
(i.e. Contacts.mdb, Sales.mdb, Purchases.mdb, jobCosting.mdb, etc.)??? If
it matters, some point in the future I'll probably upsize to SQL... just
because guitar players like to dink around )

Lynn, thank you very much for holding my hand

yisturday i didint no howe ta spel aplikasion divelupper... taday i r
wun.

THANX again for your time... (^_^)

 

[Lynn Trapp]

Since Access has to maintain an index anyways (in either of the above

examples) I figured why not just have the table's primary composite keep
all
companies and their related divisions together (saving Access from one
additional sort maintenance).

Like I said, it should work with a composite primary key but isn't
necessary. It isn't any more difficult for Access to maintain a composite
unique index, though.

One good reason I can think of to put
DivisionID(PK) first in line is to make use of Access' autoNumber
property

I don't quite follow you here. Being first in line has nothing to do with
whether or not you can use an Autonumber datatype. You are simply limited to
one Autonumber field per table.

Pdox had the autoIncrement data type also but (for lock control reasons)
it
was not favored in a networking environment (a requirement for my app) so
the standard procedure was to generate primary keys through a utility
table
on the side (where locks could be controlled). Does Access' autoNumber
data
type "really" have networking lock issues secured (no two users anywhere
on
the the network can ever generate the same primary key at the same time...
period... no ifs, ands, or buts) or is it still wiser to generate primary
keys thru a utility table on the side?

I don't think that is a problem with Access's Autonumber fields. I suppose
it is conceivably possible that two people could start creating a new record
in a table at precisely the same moment, but it's not likely. Generating the
next value for the Autonumberfield as soon as the user starts typing in any
field helps to prevent that. Paradox may have waited until the record was
committed -- don't remember.

Please refresh my memory (I'm sorry, I really did read every single page
of
the first four parts of Viescas' book... but I read it really fast). Are
you saying that (even though during the table build process I designate
BOTH
CompanyID and DivisionID to be PKs) there's an additional step I need to
take to let Access know this is in fact a compound PK (Access doesn't
assume
it)?

No, I'm not saying that. If you selected both fields -- CompanyID and
DivisionID -- and clicked the Key button, then they become the compound PK.
I'm talking about just creating a compound Unique Index NOT a compound
Primary Key.

That is how I designed my Contacts table back in the 80's. The following
is
a partial of that table structure:
tblID
IDnum (smallInt, PK)
LastName (alphanumeric80) <-- or company name
FirstName (alphanumeric50) <-- or company division
MidName (alphanumeric20)
Sex (alphanumeric1) <-- M=Male, F=Female, B=Mr & Mrs, C=company

I really don't like that at all. I think you will be happier if you stick
with the approach we have been discussing here and, possibly, add a type
field to the Companies table...

One thing I'm nervous about with this approach is... I have a feeling
(sometime, somewhere to perform some database related job WHILE keeping
everything tight and normalized)... I'll have to reference a table to
itself... and (currently) I'm having a hugh problem grasping the
"constructive use" of that concept (with databases).

I'm not sure, at this point, that I can conceive of a time when you might
need to have a self-reference in your tables, but it's not that hard to do,
as long as you set up your table for it from the beginning.

Every record in this table will have, either, a CompanyID/TelephoneID,
DivisionID/TelephoneID, or ContactID/TelephoneID combination but no 2 of
those. I can then get to all Company specific telephones by querying where
DivisionID Is Null and ContactID is null, etc.
====================
The "but no 2 of those" part threw me. I know it's probably some
rediculously simple concept but could you explain that again... slower...
for me... please... (easy on me, it's been years since I've been working
"routinely" with basic database procedures, methods and rules)

Well, basically, it means that every record in this table will have a
TelephoneID AND ONE of the other three ID's. Any one TelephoneID will be
connected either to ONE CompanyID, ONE DivisionID, or ONE ContactID.

OK... Lynn-ette

Ref: tlkpContactEventTypes
Column = ContactEventFollowUpDays
Data Type = smalint
Length = 2

Length = 2 leads me to believe that 99 is the largest integer allowed in
the
field but tableview shows entries for 360 (I was also able to modify a rec
and post a value of 4500). What am I misunderstanding?

I'm afraid you lost me here. There is no Length property for a field in an
Access table. There is a Field Size property which, in the case of a field
of datatype Number can be set to Integer, LongInteger, Single, Double, Byte,
ReplicationID, or Decimal. I don't recall reading that in John's book and
don't have it available to look at here.

to have MULTIPLE .mdb files interact with each other. What I need to know
is (given the table sizes of my application and the fact that this app
isn't
going out to market), do I put everything into one .mdb or more than one
(i.e. Contacts.mdb, Sales.mdb, Purchases.mdb, jobCosting.mdb, etc.)??? If
it matters, some point in the future I'll probably upsize to SQL... just
because guitar players like to dink around )

It depends on a couple of factors. (1) The total size of the database file.
If it starts to exceed 2Gb, then you may need to consider moving different
modules to other backend files. (2) Do you need to enforce referential
integrity between the multiple modules? If so, then you cannot do that
across .mdb files.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Frank Cutre]

Hi Lynn,

I don't quite follow you here. Being first in line has nothing to do with
whether or not you can use an Autonumber datatype. You are simply limited to
one Autonumber field per table.

The old Pdox7 autoIncrement datatype had to be the first field in the table
(if my memory serves me correctly). Access' autonumber is sounding cooler.

I don't think that is a problem with Access's Autonumber fields. I suppose
it is conceivably possible that two people could start creating a new record
in a table at precisely the same moment, but it's not likely. Generating the
next value for the Autonumberfield as soon as the user starts typing in any
field helps to prevent that. Paradox may have waited until the record was
committed -- don't remember.

Now autonumber is starting to sound really cool.

No, I'm not saying that. If you selected both fields -- CompanyID and
DivisionID -- and clicked the Key button, then they become the compound PK.
I'm talking about just creating a compound Unique Index NOT a compound
Primary Key.

Got it! I know this as a secondary index.

I really don't like that at all. I think you will be happier if you stick
with the approach we have been discussing here and, possibly, add a type
field to the Companies table...

Phew! Good... I really didn't want to use my old design.

I'm not sure, at this point, that I can conceive of a time when you might
need to have a self-reference in your tables, but it's not that hard to do,
as long as you set up your table for it from the beginning.

Got it!

Well, basically, it means that every record in this table will have a
TelephoneID AND ONE of the other three ID's. Any one TelephoneID will be
connected either to ONE CompanyID, ONE DivisionID, or ONE ContactID.

Thank you.

My wife might object if you call me that... <g>

Opps!!!! The yoke is on me... it was rude of me to assume.. sorry.

I'm afraid you lost me here. There is no Length property for a field in an
Access table. There is a Field Size property which, in the case of a field
of datatype Number can be set to Integer, LongInteger, Single, Double, Byte,
ReplicationID, or Decimal. I don't recall reading that in John's book and
don't have it available to look at here.

Boy... I'm on a roll here. This is another opps! The problem is with a
"SQL" table (Viescas' snuck in a section on SQL in the book)... never
mind... sorry... again.

It depends on a couple of factors. (1) The total size of the database file.
If it starts to exceed 2Gb, then you may need to consider moving different
modules to other backend files. (2) Do you need to enforce referential
integrity between the multiple modules? If so, then you cannot do that
across .mdb files.

If I close in on 2Gb the app goes to .adp. Thanks for the heads up on
referential integrity.

OK... Lynn-inator... that about does it for now. I'm anxious to get into
trouble (finish up the table layout in Visio then start building some
tables, forms and reports)... then, it's on to the VBA section of Viescas'
book... oh boy... just what I needed... to learn another programing
language ) I've already started playing around with ExcelVBA. Hopefully
AccessVBA will be similar commands and syntax.

You've been doing a GREAT job with me. THANK YOU very very much.

THANX again for your time... (^_^)

 

[tina]

OK... Lynn-inator... that about does it for now. I'm anxious to get into

trouble (finish up the table layout in Visio then start building some
tables, forms and reports)... then, it's on to the VBA section of Viescas'
book... oh boy... just what I needed... to learn another programing
language ) I've already started playing around with ExcelVBA. Hopefully
AccessVBA will be similar commands and syntax.

You've been doing a GREAT job with me. THANK YOU very very much.

THANX again for your time... (^_^)

looks like Lynn has gotten you to a place where you feel confident in moving
on, Frank. and thank heaven he stepped in to help! because i've yet to come
up with anything that looks workable and supports those complex real-world
relationships of yours. <wry grin>
good luck as you continue developing!

 

[Lynn Trapp]

The old Pdox7 autoIncrement datatype had to be the first field in the

table
(if my memory serves me correctly). Access' autonumber is sounding
cooler.

If your memory works like mine, we are both in trouble <g>. I remember
Paradox requiring the Primary Key to be the first field in the table but
didn't remember an autoincrement having to be first, but maybe it did. As
for Access AutoNumber fields, you will find them to be a mixed bag of
tricks. I really don't like them but use them in tables for which it is
extremely difficult to come up with a workable natural key. They do provide
a quick and easy way to provide relationships between tables, but they can
very easily mask the existence of redundant data in a given table.

Opps!!!! The yoke is on me... it was rude of me to assume.. sorry.

No worries. It happens to me all the time. At least my parents didn't do me

OK... Lynn-inator... that about does it for now. I'm anxious to get into
trouble (finish up the table layout in Visio then start building some
tables, forms and reports)... then, it's on to the VBA section of Viescas'
book... oh boy... just what I needed... to learn another programing
language ) I've already started playing around with ExcelVBA.
Hopefully
AccessVBA will be similar commands and syntax.

Have fun with VBA. You will find it to be a powerful engine -- especially
with Access. My VBA skills are a bit rusty right now, because I spend all my
time doing Oracle programming. I'm sure you will get it down.

You've been doing a GREAT job with me. THANK YOU very very much.

THANX again for your time... (^_^)

You are quite welcome. I'm glad I could help out.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html