Table Design

[debraj007]

Hello and thanks for your time. Maybe I'm making this too hard, I'm not sure.
I'm trying to help my church set up a database for the school projects. My
original thought was to have tblVolunteers and tblProjects.

However, for the kids projects, there could be M:M and with adult projects
it would be 1:1. Should I set up one table for adut volunteers and one for
children volunteers because of the realtionships?

Thanks, Deb

 

[BruceM]

In real-world terms, it sounds as if each project can be associated with
many volunteers, and each volunteer may be associated with many projects, so
there is a many-to-many relationship between volunteers and projects. You
would need a junction table to resolve that relationship, as Jamie
suggested. His code may well establish the tables and relationships, but I
have to admit I can't figure out what to do with his CREATE TABLE code. I
probably should, but it does not seem to be documented in Access Help, and
everything else I have been able to find about it assumes advance knowledge
of what to do with it.
I don't see why you would use a different relationship when the volunteers
are children. Another consideration with a separate table for children is
that they may one day be adults within the same system. A child volunteer
may one day be an adult volunteer.
I can offer some pointers on settting up a many-to-many relationship, if you
like, but I will wait to hear more about the details of what you are trying
to do.

 

[debraj007]

Sorry, to clarify a little more, there are kids projects and parents
projects, not necessarily the same projects. But the project table will hold
the same information no matter if it is a kids project or parent project.

For the kids projects, many children will voluteer for many projects. For
the parents projects only one parent will complete one project.

Deb

 

[BruceM]

"I'm now wondering whether the business rules are more like, 'each project
must have one and only one adult volunteer ('leader')" and "an adult can
only lead one project", hence the 1:1 relationship"

It never occurred to me that an adult would be limited to leading one
project in his or her lifetime. I doubt that is the case, so it could be a
one-to-many from Volunteers to Projects. However, since there is one
Projects table, and since children may one day assume Adult status within
the church, it may make sense to have a single table for all volunteers.
That approach would make it necessary to use an internal join within the
table (or whatever exactly the join is called) to keep track of families,
but could offer some advantages over having to re-enter a name into the
Adult table once majority is attained.

As I said, I still don't know what to do with the CREATE TABLE code. I
tried it in the immediate window and received the notice:
Expected: Go
I tried pasting it into a standard module, and it wouldn't compile. Since
it won't compile there is no point in attempting it as command button code
or whatever. It is undocumented in Access Help. There is something in the
KB about it, but I don't have time right now to sort out the syntax.

 

[BruceM]

If one parent will only be associated with one project ever, there could be
a one-to-one relationship. However, if a parent can be associated with
several projects over time, one parent => many projects, or one-to-many.
Tables of names are often used for all projects, committees, and so forth.
If a name or address changes, it is necessary to update only one record. If
what you have here is a standalone project that does not interact with other
projects, and if there is no need to keep track of the parents of the
children or vice versa, or the relationship of children to each other
(brothers and sisters in particular), you could create two volunteer tables
(adults and children) with two different kinds of relationships.
For a many-to-many relationship you would need three tables:

tblVolunteer
VolunteerID (primary key, or PK)
FirstName, etc.

tblProject
ProjectID (PK)
Description, etc.

tblProjectVolunteer
ProjVolID (PK)
VolunteerID (foreign key, or FK)
ProjectID (FK)

Create relationships between the two VolunteerID fields and the two
ProjectID fields. Click Enforce Referential Integrity. Base a form on
tblProject, with a subform based on tblProjectVolunteer. On the subform you
can have a combo box bound to VolunteerID. The combo box row source comes
from tblVolunteer.

With this setup you can enter project information or select an existing
project, and add names to it. Note that even if there is just one volunteer
if the volunteer is an adult, the same setup can be used. A many-to-many
relationship does not need to have "many" at both ends. A project can have
a single volunteer even though the database is set up to accept multiple
volunteers.

 

[BruceM]

All I said was that I don't know how to use the syntax. A search in VBA
help for "CREATE TABLE" turns up not a single result. Turns out it is in
regular Access help, so it's not a VBA thing. That would have been helpful
to know, especially when it was so clear I was heading down the wrong road.

Anyhow, I found the ANSI-92 mode, and changed to that, and created one of
the three tables in your code by pasting into a query. The other two had a
syntax error and something else about not finding the table or constraint.

I have bookmarked the articles, and will study them when I can. For a
syntax for creating tables see my latest response to the OP in this thread.
No doubt CREATE TABLE is a fine thing, but it's not especially helpful when
it's so difficult to glean enough information to learn how to implement it.

[Jamie's] code may well establish the tables and relationships, but I
have to admit I can't figure out what to do with his CREATE TABLE code. I
probably should, but it does not seem to be documented in Access Help, and
everything else I have been able to find about it assumes advance
knowledge
of what to do with it.

Some general points:

· 'CREATE TABLE' syntax, a subset of SQL DDL, has been in Jet since at
least version 2, hence for at least 12 years; and is documented in the
Access Help; if you are experiencing difficulties in finding it,
here's a URL (Access2003 Help):

http://office.microsoft.com/en-gb/access/CH010410161033.aspx

· The enhanced Jet 4.0 SQL DDL (e.g. to include CHECK constraints) I
posted here has been in Jet since version 4.0, hence at least six
years, and has been available via the Access user interface (UI) since
Access2002, hence at least four years. If it is new to you, see:

Intermediate Microsoft Jet SQL for Access 2000
http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx

(Note it's classed as 'intermediate' rather than 'advanced').

· To use Jet 4.0 SQL DDL in the Access UI you must put the database
into ANSI-92 Query Mode (which I name-checked in my original post)
which is documented in the Access Help; if you are experiencing
difficulties in finding it, here's a URL:

http://office.microsoft.com/en-gb/access/HP030704831033.aspx

· Posting SQL DDL is considered netiquette in other SQL groups and is
a very efficient way of describing a schema (table structure) in a
newsgroup posting.

As regards the SQL DDL I posted, you can execute each SQL DDL
statement (delimited by semicolons) while in ANSI-92 Query Mode and
examine the results in the Access UI.

[I used to always wrap my SQL DDL in an short VBA routine, using ADO
to execute each statement, but I got no feedback so I stopped doing so
every time because it takes several minutes.]

Alternatively, and perhaps the better approach, is to examine the code
and recreate the database objects using the Access UI: there's a
designer for tables where a UNIQUE constraint can be implemented using
an 'index' and a CHECK constraint may be implemented via 'field'
Validation Rule, whereas a FOREIGN KEY can be defined using 'drag and
drop' in the Relationships window

....although I have the impression you're my grandmother, BruceM, and
I'm describing how eggs may be sucked <g>, in which case here's a
challenge: describe in words how I can create the same tables,
constraints and relationships i.e. avoiding SQL DDL. Bear in mind my
original post took about ten minutes in total.

Jamie.

--

 

[BruceM]

I maintain that a one-to-one relationship makes sense ONLY if a volunteer in
the course of involvement with an organization is associated with one and
only one project. I repeat that it never occurred to me that a volunteer
organization would place such a constraint on its volunteers.
All sorts of things might have occurred to me. The one-to-one that the OP
mentioned is one I addressed. Yes, the OP said one-to-one. No argument
from me about what they said. For reasons already mentioned I wondered if
the OP really intended a one-to-one, or if there was a misunderstanding
about the term.
You made certain assumptions, as did I, in the absence of definite
information one way or the other. Whether or not the leader is overwritten
in the project table, doesn't a one-to-one limit that person to a single
project?
The OP seems to be a relative beginner. As such it seems appropriate for me
to mention things that may be important, even if they were not mentioned.
There are two main schools of thought on the use of autonumbers as
stand-alone PKs. We hold different viewpoints. I will continue to suggest
autonumbers where I think it is appropriate. You think it is more
responsible to let the OP try to sort it out. We will have to disagree
about that one.
I will study the links about SQL. I realize there are considerable gaps in
my knowledge. However, I still have a job to do, even if I don't know
everything I should.

 

[BruceM]

So the CREATE TABLE code it is intended primarily as a way of describing the
table structure rather than as code to be used to create a table?

 

[BruceM]

Correct but there's a fundamental difference between 'limited to one
in the current state' and 'limited to one throughout the person
entity's lifetime'.

I guess I don't see the fundamental difference, probably because you lost me
at "current state".

FWIW I know of three:

1) As an artificial key to be exposed to users; justification: no
natural key is available.

I had forgotten about this one. An example may be a help desk database in
which a tracking number is needed, but the number needs to meaning beyond
uniqueness.

2) As a surrogate to a natural key and never to be exposed to users;
justification: the natural key ensures data integrity but for some
reason it is inefficient for use in foreign keys and joins.
3) Avoid autonumbers; justification: a natural key usually exists if
you research hard enough (e.g. an industry standard key) and if all
fails you can invent your own including user-friendly features such as
a check-digit, pseudo-random values that are far apart, etc.

I have seen the situation where EmployeeID numbers are changed to another
format (an extra digit, or a prefix, or whatever). The EmployeeID is
unique, but it turned out to be changeable. I know that I can do cascade
updates, but I wonder if that is better than just using a number that has
meaning only to the database. The EmployeeID can still be indexed, no
duplicates (if that is what you mean by "unique constraint"), but I would
rather avoid the extra work of updating all related records if the value
changes.

Regarding the quoted posting, I take the main point to be that an autonumber
should not be seen as a substitute for indexing and enforcing uniqueness.
People sometimes use autonumber and don't give further thought to the
integrity of their data. I can see where autonumber could lead to lax
habits in organizing the structure. No argument here. I have looked back
over projects and realized that indexing was limited almost entirely to the
autonumber field (or to other number fields) to the detriment of
performance.