Microsoft Access Database Design

  • Thread starter Thread starter ixeye
  • Start date Start date
I

ixeye

I am creating a database for church and you would not think that
what I am doing is reinventing the wheel but gosh if it feels
like it :) The database is being created to track who is coming
to our church and when so I originally created two tables: one
for all the info on a particular someone and two for the dates.
We don't have a unique ID number that we can use for someone
specific to link the two tables so I finally chose to create one
big table with all the personal info and with all the dates
that they attended as Yes/No fields. 2 problems arose from doing
it this way. The number of fields is huge if I talk about one
field per date and then making an automated report is almost
impossible since parameter queries I believe are not created
for variable fields only variables in each field??? Any ideas,
anybody on the best way to design this database?
 
My Church program has 22 tables in it.

I suggest you find someone who is an Access expert and can help you build it
correctly.
 
You are in for *major* headaches if you try to do it this way! You don't
need a unique ID number for each person but you do need a way to uniquely
identify them - otherwise how will your user(s) select the correct person to
enter attendance information? IOW, you really have to come up with some
combination of fields that constitutes a unique key - whether you use that
as the primary key for the table is up to you. I personally prefer to create
an autonum field which serves as a surrogate key, then create a unique index
on the field(s) that make up the unique key for the table.

Now, regarding the attendance information, you really need to put this into
a separate table - PersonAttendance. This way you merely add a new record
for each date the person attends a church function. Otherwise, you have to
make table, form and report changes to accomodate every new date. Also, you
will eventually hit the limit on # fields per table and as you've already
discovered, reporting is difficult with this highly unnormalized design.
 
Would you consider sending me your Church MDB file (without personal
info of course) so I could look at how you have set up the fields and
relationships?
Brent Carey
(e-mail address removed)


Sandra Daigle said:
You are in for *major* headaches if you try to do it this way! You don't
need a unique ID number for each person but you do need a way to uniquely
identify them - otherwise how will your user(s) select the correct person to
enter attendance information? IOW, you really have to come up with some
combination of fields that constitutes a unique key - whether you use that
as the primary key for the table is up to you. I personally prefer to create
an autonum field which serves as a surrogate key, then create a unique index
on the field(s) that make up the unique key for the table.

Now, regarding the attendance information, you really need to put this into
a separate table - PersonAttendance. This way you merely add a new record
for each date the person attends a church function. Otherwise, you have to
make table, form and report changes to accomodate every new date. Also, you
will eventually hit the limit on # fields per table and as you've already
discovered, reporting is difficult with this highly unnormalized design.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I am creating a database for church and you would not think that
what I am doing is reinventing the wheel but gosh if it feels
like it :) The database is being created to track who is coming
to our church and when so I originally created two tables: one
for all the info on a particular someone and two for the dates.
We don't have a unique ID number that we can use for someone
specific to link the two tables so I finally chose to create one
big table with all the personal info and with all the dates
that they attended as Yes/No fields. 2 problems arose from doing
it this way. The number of fields is huge if I talk about one
field per date and then making an automated report is almost
impossible since parameter queries I believe are not created
for variable fields only variables in each field??? Any ideas,
anybody on the best way to design this database?
Click to expand...
Click to expand...
 
As soon as I get a chance I'll send you a snapshot of the relationships
diagram - I don't have a current copy at my fingertips right now but I will
get back to you with this as soon as I can.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Would you consider sending me your Church MDB file (without personal
info of course) so I could look at how you have set up the fields and
relationships?
Brent Carey
(e-mail address removed)


Sandra Daigle said:
You are in for *major* headaches if you try to do it this way! You
don't need a unique ID number for each person but you do need a way
to uniquely identify them - otherwise how will your user(s) select
the correct person to enter attendance information? IOW, you really
have to come up with some combination of fields that constitutes a
unique key - whether you use that as the primary key for the table
is up to you. I personally prefer to create an autonum field which
serves as a surrogate key, then create a unique index on the
field(s) that make up the unique key for the table.

Now, regarding the attendance information, you really need to put
this into a separate table - PersonAttendance. This way you merely
add a new record for each date the person attends a church function.
Otherwise, you have to make table, form and report changes to
accomodate every new date. Also, you will eventually hit the limit
on # fields per table and as you've already discovered, reporting is
difficult with this highly unnormalized design.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I am creating a database for church and you would not think that
what I am doing is reinventing the wheel but gosh if it feels
like it :) The database is being created to track who is coming
to our church and when so I originally created two tables: one
for all the info on a particular someone and two for the dates.
We don't have a unique ID number that we can use for someone
specific to link the two tables so I finally chose to create one
big table with all the personal info and with all the dates
that they attended as Yes/No fields. 2 problems arose from doing
it this way. The number of fields is huge if I talk about one
field per date and then making an automated report is almost
impossible since parameter queries I believe are not created
for variable fields only variables in each field??? Any ideas,
anybody on the best way to design this database?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top