Database design problem

[Guest]

I need to develop a mailing list database for stakeholders.
The details of sal, title, names, address, position, company etc are pretty
standard.

The mail list needs to sort on attributes such as,
Federal politician
State politician
Local government politician
Local government employee
Tour operator
Friends group
Board 1 member
Board 2 member
Annual review committee member
Walking group
Volunteer 1
Volunteer 2
Newspaper contact
Working group member
Birds group
Etc, etc,

The tricky bit for me is that a person may be a member of 3 of the attribute
groupings from the list above. How can I represent this sort of relationship
without entering a person’s name 3 times, once for each attribute they are
associated with or enter 3 different attributes for the same person. At
present the database is an excel spreadhseet and is hopelessly complex to
use. What options are there for setting up the tables for this database?

 

[Guest]

Hi, Liz.

What you have is a one-to-many relationship between Stakeholder and their
type. To handle it, you need 3 tables:

Stakeholders
SHID AutoNumber (Primary Key)
Name
Address
City
State
Zip
etc.

ContactTypes
CTID Autonumber (Primary Key)
ContactType Text ("Local Politician", "State Politician", etc.)


StakeholderTypes
SHTypeID AutoNumber (Primary Key)
SHID Numeric (Foreign Key to Stakeholders)
TypeID Numeric (Foreign Key to ContactType)

Create a tabular form for it containing the SHID and TypeID only, and then
insert it as a subform onto your main Stakeholders form, or another one based
on Stakeholders, if you prefer.

When you go to do your mailing, assuming you want to send each person only
one copy, even if they're listed more than once, create a query, and specify
Unique Values only, then base your report on the query.

Hope that helps.
Sprinks

 

[Mike Labosh]

The tricky bit for me is that a person may be a member of 3 of the
attribute

groupings from the list above. How can I represent this sort of relationship
without entering a person’s name 3 times, once for each attribute they are
associated with or enter 3 different attributes for the same person. At
present the database is an excel spreadhseet and is hopelessly complex to
use. What options are there for setting up the tables for this database?

I will assume that your "persons" are in a "Person" table that has an
autonumber primary key called PersonKey...

Make a Title table with a autonumber and description columns:

Title:
TitleKey AutoNumber
Description Text (50)

And put these in it:

TitleKey Description
1 Federal politician
2 State politician
3 Local government politician
4 Local government employee
5 Tour operator
etc...

Then Make a PersonTitle table with a foreign key to your person records and
a foreign key to your Title records, and let the primary key be the
composite of the two columns:

PersonTitle:
PersonKey Long Integer
TitleKey Long Integer

And the data might look like this:

PersonKey TitleKey
1 4
1 5
2 3
3 1
3 5

Then to put the person's name next to a list of his/her titles, you can do
this:

SELECT FirstName, LastName, Description
FROM Person
INNER JOIN PersonTitle
ON PersonTitle.PersonKey = Person.PersonKey
INNER JOIN Title
ON Title.TitleKey = PersonTitle.TitleKey

Thanks! That was fun!
--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS!"
~~ General Barringer ~~

 

[Guest]

thanks, this is just what I needed.

Hi, Liz.

What you have is a one-to-many relationship between Stakeholder and their
type. To handle it, you need 3 tables:

Stakeholders
SHID AutoNumber (Primary Key)
Name
Address
City
State
Zip
etc.

ContactTypes
CTID Autonumber (Primary Key)
ContactType Text ("Local Politician", "State Politician", etc.)


StakeholderTypes
SHTypeID AutoNumber (Primary Key)
SHID Numeric (Foreign Key to Stakeholders)
TypeID Numeric (Foreign Key to ContactType)

Create a tabular form for it containing the SHID and TypeID only, and then
insert it as a subform onto your main Stakeholders form, or another one based
on Stakeholders, if you prefer.

When you go to do your mailing, assuming you want to send each person only
one copy, even if they're listed more than once, create a query, and specify
Unique Values only, then base your report on the query.

Hope that helps.
Sprinks