Database design

[Tim Moynihan]

I have a problem with multiple many-to-manys. I am trying to design an
application whereby I have all my contacts in one table. This is for the ease
in editing and maintaining my lists. The problem I face is that some of my
students qualify for all the following tables; ContactName(master list),
homework, subjects, class, buyer, and supplier. Except for the ContactName
all the tables are many-to-many. How do I resolve this problem? Any
assistance you can give will be much appreciated.

 

[Jason Lepack]

I have a problem with multiple many-to-manys. I am trying to design an
application whereby I have all my contacts in one table. This is for the ease
in editing and maintaining my lists. The problem I face is that some of my
students qualify for all the following tables; ContactName(master list),
homework, subjects, class, buyer, and supplier. Except for the ContactName
all the tables are many-to-many. How do I resolve this problem? Any
assistance you can give will be much appreciated.

The general way of dealing with a many-to-many relationship is with a
junction table.

contacts:
contactID
contactName

homework:
homeworkID
homeworkName

subject:
subjectID
subjectName

class:
classID
className

So now, in the example I listed above, if there were many to many
relationships between contacts and homework, contacts and subject,
contact and class then I would need these three tables:

junct_contact_homework:
homeworkID
contactID

junct_contact_subject:
subjectID
contactID

junct_contact_class:
classID
contactID

Cheers,
Jason Lepack

 

[Tim Moynihan]

Hi Jason,
Many thanks for your reply. I really appreciate it but unfortunately it does
not solve my problem. On my single table database I have 28 categories and
some of my contacts qualify for multiple categories. I have tried two methods
of filtering by QBE without success. The first was to refer to my category
table and type into the criteria row 27 And 28. The two entries cancelled
each other out; 27 Or 28 worked well for an "either/or" but failed for three
combinations or more.

I then created new Yes/No fields for the different disciplines that I want
to combine but this also failed. They could handle an "OR" statement but not
an "AND" and completely failed with three or more options.

You've guessed it. I am a struggling beginner trying to swim in deep water
and I am having a hard time. I want to know how I can select each of my
Contacts who qualify for SAY; Student, Buyer, Seller, LibraryMember,
CharityOrganiser as they are a select band who deserve special recognition.

I have three books that cost me over $500 and none cover this topic!

 

[John W. Vinson]

Hi Jason,
Many thanks for your reply. I really appreciate it but unfortunately it does
not solve my problem. On my single table database I have 28 categories and
some of my contacts qualify for multiple categories. I have tried two methods
of filtering by QBE without success. The first was to refer to my category
table and type into the criteria row 27 And 28. The two entries cancelled
each other out; 27 Or 28 worked well for an "either/or" but failed for three
combinations or more.

Reread Jason's suggestion.

Your table design IS WRONG. Jason's design is much preferable. You're
"committing spreadsheet" by having different FIELDS for the categories. What
you need is different RECORDS.

I then created new Yes/No fields for the different disciplines that I want
to combine but this also failed. They could handle an "OR" statement but not
an "AND" and completely failed with three or more options.

You've guessed it. I am a struggling beginner trying to swim in deep water
and I am having a hard time. I want to know how I can select each of my
Contacts who qualify for SAY; Student, Buyer, Seller, LibraryMember,
CharityOrganiser as they are a select band who deserve special recognition.

I have three books that cost me over $500 and none cover this topic!

Look up "Many To Many" in the index. If the index doesn't cover the concept, I
can't say much for the book.

John W. Vinson [MVP]

 

[Tim Moynihan]

Hi John,
I bought Microsoft Access 2003 - Forms, Reports and Queries from Que
Publishing, Financial Modelling using Excel and VBA from Wiley, and Teach
Yourself from Sams Publishing. They all teach me how to handle say, one
teacher many students/one student many teachers but they do not show me how
to place ALL my contacts in one table and then to treat them as different
personalities. Even Microsoft's "Northwind" places a name field in each of
the Buyer and Seller tables which indicates to me that if Tom Jones is both a
Buyer and a Seller then I have to enter his name in each table. This is what
I am trying to avoid. I only want to enter his name once into my application.

Anyway, many thanks for your interest and response.

 

[Jason Lepack]

I understand your problem now. You want to link the same table to
multiple other tables. That's fine, you can do that, no problem.

The Northwind Database clearly wasn't expecting any person to be a
customer as well as an employee.

However, let's use Northwind as an example. You could create a new
table, contacts, with any fields that you thought were relevant to all
people (name, address, phone number) and a contactID. Next you could
replace all information about the people in the Customers and
Employees tables with contactID. Next click on Tools->Relationships
and create relationships between Customers.contactID -
Contacts.contactID and Employees.contactID - Contacts.contactID.

If that's not what you're talking about, then help us help you.
Describe what you're trying to do. Remember, we're not there. We
can't see what you're thinking or doing. If you don't describe your
problem clearly then we can't help you.

Cheers,
Jason Lepack

 

[Immanuel Sibero]

Tim,

In addition to the other good responses, maybe the following can help:
http://allenbrowne.com/AppHuman.html

It's a quick tutorial by Allen Browne to model various human relationships.
One of the objectives is the same as yours - to have one table for *person*
(i.e. so that one person is entered only once) even though a person may take
multiple roles.


HTH,
Immanuel Sibero