Hey all-
I am working on a half-finished database designed by someone else. One
of the jobs of the DB is to manage contacts (customers, vendors,
etc.). The relational structure that was in place when I began work on
this project was as follows: A "Company" table related to many
"Location" tables, which in turn are each related to many "Contact"
tables. Seems pretty straightforward.

The problem is, one contact should be able to be associated with
multiple locations. As an example, A person may be associated with one
shipping location, and a different location for billing. Or, a single
contact may sometimes require shipments to their offices in NYC, and
sometimes to their home in CT. With the current structure, this would
require two Contact records with the same name, each associated with a
separate location. Unless I'm missing something.

My question: Is there something inherently wrong with this structure,
or is there something wrong with the way I'm thinking about it? I'm
leaning towards the latter, as this FileMaker solution, although not
finished, was designed by a well known and respected consulting firm.

So can I make this work, or do I have to change the structural design?

Thanks in advance and for all your previous help,
Nate

Nate,

sorry to say there is something wrong with the way you are thinking.

basicaly you need a relation for each line you discribe. OR create a one to
many relationship that uses a linktable.
Generaly I would choose the linktable option, but this is more complex to
set up.

Eitherway I hope your table do have unique ID's that can be used to link the
tables.

Ursus

"NScheffey" <NScheffey (AT) gmail (DOT) com> schreef in bericht
news:1173451500.037419.182090 (AT) q40g2000cwq (DOT) googlegroups.com...

On Mar 9, 1:21 pm, "Ursus" <ursus.k... (AT) wanadoo (DOT) nl> wrote:
Hmmm, maybe I didn't express myself clearly. There IS a relation in
place for each line. Each table DOES have a unique ID which is used as
a primary key. This is the way it is currently set up (not designed by
me):

Company:
KP (primary key)

Location:
KP (primary key)
kf_company (foreign key relating to company primary key)

Contact:
KP
kf_location (foreign key relating to location primary key)

This is the structure I'm talking about that I don't think is correct.
You are saying that with this set-up I can relate a Contact to many
Locations? How?

Nate

On Mar 9, 1:21 pm, "Ursus" <ursus.k... (AT) wanadoo (DOT) nl> wrote:
Hmmm, maybe I didn't express myself clearly. There IS a relation in
place for each of these "lines", and each table does have a unique ID
for use as a primary key. This is how it is currently set-up:

Company:
KP (primary key)

Location:
KP (primary key)
kf_company (foreign key relating to company KP)

Contact:
KP (primary key)
kf_location (foreign key relating to location KP)
kf_company (foreign key relating to company KP)

This is the structure that I'm thinking is inherently flawed. You are
saying I can relate one Contact to many Locations with this structure?
How?

Nate

On Mar 9, 11:35 am, "NScheffey" <NSchef... (AT) gmail (DOT) com> wrote:
Take out the kf_location in the Contact table and add a kf_contact to
the Location table.

G

Nate,

Nope, the way it is set up now, you can onley add one items to each record.
Either you add fields (and relations) untill you are satisfied you have
enough
OR
you add an intermediate (link) table between contacts and locations.
TableLinkCompanyLocation
start with two fields (later you will add more)
kf_company and kf_location
create a calculation that creates a compoundkey (kf_company & "_" &
kf_location)

now for each location & company combination you create a new record and put
the correct ID in the correct field.

So now you have set up the link, now you will want to use some information
from either or both tables. Create a calculation for each of the field syou
need. Say you want from location to appear the city field in a portal in the
company tabel. You create a calc field in the link table cLocCity < text ,
loaction::city >
Now you create a relation between location::kf_company and Link::kf_company
and you can create a portal on a company layout that shows all the current
related locations

Keep well, ursus

"NScheffey" <NScheffey (AT) gmail (DOT) com> schreef in bericht
news:1173465350.889639.306170 (AT) c51g2000cwc (DOT) googlegroups.com...