db design question - providing fields for Key People - Best way = ???

[MyEmailList]

I normally setup a "company" table and a child table named "people"
for people assoicated with the company

In the people table one can enter as many people for that company as
they want...

CEO, GM, CFO, Sales Mgr, Accounts Payable Manager, Account Rep,
Whatever

so each person is in a seperate record that is linked to the company
record.

The problem with this approach is that you can't "force" capture of
key people.

Now we want to design the database with defined fields for key people
like the CEO, GM, and a few others

The problem is it takes about a dozen fields for each person... Fname,
MI, Lname, email, phone, fax, address, city, state, zip, official
title, job function, OK_to_Email, Reports_To, Notes Field, etc

If I put this in the "Company" table so we can speify that each
position must have a name in it (and associated data)... then the
record for that "Company" gets big... has lot's of fields

And, we still may need the "People" table for other incidential people
we must keep up with.

My question... how would you design the database to "force" the
capture of data for key people and associate it with a particular
company... AND still allow for other names to be entered?

Would you put the company data and key people in the same record...
and other people in the "other people" table... or what?

thanks for any help.

Mel

 

[Albert D. Kallal]

I would simply just add another column to the "people" table


eg:

FirstName LastName PType
Albert Kallal CEO
Joe Smith Sales Mgr

etc. etc....


Of course, to save drudgery,a nd typing, the Ptype field could certainly be
a combo box, ad you could create a table of people types..but, those types
of "combo" box fields are not really a relational issue in as much as a easy
to enter/type issue.

If the person is not a "special" type, then you can default the field to
"regular", or perhaps even allow it to be blank....

At any rate...we talking about only adding one field here.....

 

[John W. Vinson]

We considered that approach.

But we want to be able to show the "key people" on a blank form... so
that the user knows they must research and fill in each one... for
example

Company Name
Company Address
Company ETC
CEO Name
CEO Email
CEO Etc
GM Name
GM Email
GM Etc
Decision_Maker Name
Decision_Maker Email
Decision_Maker_etc

The problem with simply providing a "title" field in the "people"
table is that that it offers the user the option of not entering all
of the "key" people data we want... they may just enter the CEO or
just the GM... or whatever.

One way that you can do this - without putting redundant data fields in the
Company table - is use a Subform based on a query. Have a table of KeyTitles
(CEO, GM, etc.); create a query joining it to the Contacts table, joining by
title. Use a *left outer join* so that you see all records in the KeyTitles
table, and matching records in the Contacts table. Be sure to include the
Title field from *both* tables in the query.

If you base a subform on this query, you can enter the name, address, email,
phone, etc. into the Contacts field; the Title will fill in automatically
("row fixup") from the KeyTitles table.

John W. Vinson [MVP]