Database Design Questions

[William Wisnieski]

Hello Everyone,

I'm really stuck on how to design this application, so I thought I'd see if
anyone had any general ideas on how to proceed. I'd say I'm an intermediate
level Access developer.

I volunteered to help my kid's school (a small non-profit) with a tremendous
need they have for a complete student administration database. I've
developed fairly complex databases before but this one is very unique in how
it must be designed.

The challenge is this. A record must be created that contains information
about the student and both parents (tracking begins with an inquiry, then
admissions, then enrollment). Then, the database must contain some sort of
master table or index of every individual so an administrator can search
for, say one parent because that parent may become an individual donor to
the school. So even though the parents names were entered as part of the
child's record, there must be a way to look up the parent later and have the
parent's info displayed. That goes for Staff and Alumni. I know I'll need
to build a table that will contain how people are related and affiliated to
eachother and the school, but the part that is throwing me is how to get
several names from one form/record into a master table or index so that each
individual then has their own ID. My form might look like this:
Child Record 01

Parent 1 Jane Doe
Parent 2 John Doe
Child 1 Suzy Doe

=====>

Master Table

Jane Doe 001
John Doe 002
Suzy Doe 003

There are plenty of siblings at the school. Not sure how to work around
that either.

Any design ideas to get me started would be greatly appreciated.

Thanks for your help,

William

[Allen Browne]

William, as a starting point, see:
People in households and companies
at:
http://members.iinet.net.au/~allenbrowne/AppHuman.html

Human relationships are some of the hardest things to model with simple
flexibility. The article suggests that everyone (parents, students,
teachers, alumni) goes into one table, and you can then creating grouping of
people such as households so you can track who belongs to whom.

It contains a basic sample database that illustrates the idea. One of
benefits of that simple design is that it's very easy to build on. For
example, since all the people are in one table, there is no drama regardless
of whether it comes from a child, parent, or teacher.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"William Wisnieski" <wi************ ***@verizon.net > wrote in message
news:WCSMd.1003 0$g16.5282@trnd ny08...

Hello Everyone,

I'm really stuck on how to design this application, so I thought I'd see
if
anyone had any general ideas on how to proceed. I'd say I'm an
intermediate
level Access developer.

I volunteered to help my kid's school (a small non-profit) with a
tremendous
need they have for a complete student administration database. I've
developed fairly complex databases before but this one is very unique in
how
it must be designed.

The challenge is this. A record must be created that contains information
about the student and both parents (tracking begins with an inquiry, then
admissions, then enrollment). Then, the database must contain some sort
of
master table or index of every individual so an administrator can search
for, say one parent because that parent may become an individual donor to
the school. So even though the parents names were entered as part of the
child's record, there must be a way to look up the parent later and have
the
parent's info displayed. That goes for Staff and Alumni. I know I'll
need
to build a table that will contain how people are related and affiliated
to
eachother and the school, but the part that is throwing me is how to get
several names from one form/record into a master table or index so that
each
individual then has their own ID. My form might look like this:
Child Record 01

Parent 1 Jane Doe
Parent 2 John Doe
Child 1 Suzy Doe

=====>

Master Table

Jane Doe 001
John Doe 002
Suzy Doe 003

There are plenty of siblings at the school. Not sure how to work around
that either.

Any design ideas to get me started would be greatly appreciated.

Thanks for your help,

William

[William Wisnieski]

Allen,

Thank you for the excellent information. It is a huge help.

I'm looking at your structure and wondering how I might be able to apply it
to what I'm attempting. My biggest hurdle is this:

The main user (school secretary) as been using an access database she built.
It is not relational. I'm going to build a new one from scratch but I would
at least like to follow some of the data entry logic she uses when entering
a new record. In other words, I would like the interface to (as close as
possible) duplicate how she processes a new application.

First, she enters an "Inquiry". Her current form requires her to enter both
parents first on the top of her form with contact info, then the prospective
student underneath on the same form. Using your logic and database
structure, how could I create an Inquiry form that allowed me to list the
student with BOTH parents on the same form? The contact info only needs to
be entered once on the Inquiry form . Even if the parents do not live
together, the secretary only uses whatever address the student lives at. I
don't think she even collects contact info currently on the other parent if
they live apart, but it is something they would like to start doing. I know
a subform would be the option. Would I make the form where the parents are
entered, the subform of the student? Then have contact info listed with the
student?

Thanks again for your invaluable help. I at least am starting to see the
right direction I should take.

William


Primarily, the school Secretary will enter an "Inquiry" first. On this
form, she will
"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:42******** **************@ per-qv1-newsreader-01.iinet.net.au ...

William, as a starting point, see:
People in households and companies
at:
http://members.iinet.net.au/~allenbrowne/AppHuman.html

Human relationships are some of the hardest things to model with simple
flexibility. The article suggests that everyone (parents, students,
teachers, alumni) goes into one table, and you can then creating grouping of people such as households so you can track who belongs to whom.

It contains a basic sample database that illustrates the idea. One of
benefits of that simple design is that it's very easy to build on. For
example, since all the people are in one table, there is no drama regardless of whether it comes from a child, parent, or teacher.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"William Wisnieski" <wi************ ***@verizon.net > wrote in message
news:WCSMd.1003 0$g16.5282@trnd ny08...

Hello Everyone,

I'm really stuck on how to design this application, so I thought I'd see
if
anyone had any general ideas on how to proceed. I'd say I'm an
intermediate
level Access developer.

I volunteered to help my kid's school (a small non-profit) with a
tremendous
need they have for a complete student administration database. I've
developed fairly complex databases before but this one is very unique in
how
it must be designed.

The challenge is this. A record must be created that contains information about the student and both parents (tracking begins with an inquiry, then admissions, then enrollment). Then, the database must contain some sort of
master table or index of every individual so an administrator can search
for, say one parent because that parent may become an individual donor to the school. So even though the parents names were entered as part of the child's record, there must be a way to look up the parent later and have
the
parent's info displayed. That goes for Staff and Alumni. I know I'll
need
to build a table that will contain how people are related and affiliated
to
eachother and the school, but the part that is throwing me is how to get
several names from one form/record into a master table or index so that
each
individual then has their own ID. My form might look like this:
Child Record 01

Parent 1 Jane Doe
Parent 2 John Doe
Child 1 Suzy Doe

=====>

Master Table

Jane Doe 001
John Doe 002
Suzy Doe 003

There are plenty of siblings at the school. Not sure how to work around
that either.

Any design ideas to get me started would be greatly appreciated.

Thanks for your help,

William

[Allen Browne]

Once you get a good data structure, you can interface it almost any way that
suits the user. If you really want to emulate what she currently has, you
could use an unbound main form as a holder for 3 subforms:
- Dad
- Mum
- Child
She enters both parents, and then the child.
The entries are all saved into the same Client table.
In the AfterInsert event of the subform, you could create/append to the
entries in the other table(s) that define the family household.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"William Wisnieski" <wi************ ***@verizon.net > wrote in message
news:ieqNd.5960 $sR5.2518@trndn y05...

Allen,

Thank you for the excellent information. It is a huge help.

I'm looking at your structure and wondering how I might be able to apply
it
to what I'm attempting. My biggest hurdle is this:

The main user (school secretary) as been using an access database she
built.
It is not relational. I'm going to build a new one from scratch but I
would
at least like to follow some of the data entry logic she uses when
entering
a new record. In other words, I would like the interface to (as close as
possible) duplicate how she processes a new application.

First, she enters an "Inquiry". Her current form requires her to enter
both
parents first on the top of her form with contact info, then the
prospective
student underneath on the same form. Using your logic and database
structure, how could I create an Inquiry form that allowed me to list the
student with BOTH parents on the same form? The contact info only needs
to
be entered once on the Inquiry form . Even if the parents do not live
together, the secretary only uses whatever address the student lives at.
I
don't think she even collects contact info currently on the other parent
if
they live apart, but it is something they would like to start doing. I
know
a subform would be the option. Would I make the form where the parents
are
entered, the subform of the student? Then have contact info listed with
the
student?

Thanks again for your invaluable help. I at least am starting to see the
right direction I should take.

William


Primarily, the school Secretary will enter an "Inquiry" first. On this
form, she will
"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:42******** **************@ per-qv1-newsreader-01.iinet.net.au ...

William, as a starting point, see:
People in households and companies
at:
http://members.iinet.net.au/~allenbrowne/AppHuman.html

Human relationships are some of the hardest things to model with simple
flexibility. The article suggests that everyone (parents, students,
teachers, alumni) goes into one table, and you can then creating grouping

of

people such as households so you can track who belongs to whom.

It contains a basic sample database that illustrates the idea. One of
benefits of that simple design is that it's very easy to build on. For
example, since all the people are in one table, there is no drama

regardless

of whether it comes from a child, parent, or teacher.

HTH
"William Wisnieski" <wi************ ***@verizon.net > wrote in message
news:WCSMd.1003 0$g16.5282@trnd ny08...

> Hello Everyone,
>
> I'm really stuck on how to design this application, so I thought I'd
> see
> if
> anyone had any general ideas on how to proceed. I'd say I'm an
> intermediate
> level Access developer.
>
> I volunteered to help my kid's school (a small non-profit) with a
> tremendous
> need they have for a complete student administration database. I've
> developed fairly complex databases before but this one is very unique
> in
> how
> it must be designed.
>
> The challenge is this. A record must be created that contains information > about the student and both parents (tracking begins with an inquiry, then > admissions, then enrollment). Then, the database must contain some sort > of
> master table or index of every individual so an administrator can
> search
> for, say one parent because that parent may become an individual donor to > the school. So even though the parents names were entered as part of the > child's record, there must be a way to look up the parent later and
> have
> the
> parent's info displayed. That goes for Staff and Alumni. I know I'll
> need
> to build a table that will contain how people are related and
> affiliated
> to
> eachother and the school, but the part that is throwing me is how to
> get
> several names from one form/record into a master table or index so that
> each
> individual then has their own ID. My form might look like this:
>
>
> Child Record 01
>
> Parent 1 Jane Doe
> Parent 2 John Doe
> Child 1 Suzy Doe
>
> =====>
>
> Master Table
>
> Jane Doe 001
> John Doe 002
> Suzy Doe 003
>
> There are plenty of siblings at the school. Not sure how to work
> around
> that either.
>
> Any design ideas to get me started would be greatly appreciated.
>
> Thanks for your help,
>
> William

[William Wisnieski]

Thanks again Allen. I'm plugging away at this thing this morning and I have
another question.

Is there anyway to add code to the inquiry form to automatically create a
new group after an inquiry is entered? I'm specifically wondering if a new
group can be created for the three family members (mom, dad, child) after
the user enters new inquiry data and the entries are appended to the client
table.

Thanks,

William
"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:42******** **************@ per-qv1-newsreader-01.iinet.net.au ...

Once you get a good data structure, you can interface it almost any way that suits the user. If you really want to emulate what she currently has, you
could use an unbound main form as a holder for 3 subforms:
- Dad
- Mum
- Child
She enters both parents, and then the child.
The entries are all saved into the same Client table.
In the AfterInsert event of the subform, you could create/append to the
entries in the other table(s) that define the family household.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"William Wisnieski" <wi************ ***@verizon.net > wrote in message
news:ieqNd.5960 $sR5.2518@trndn y05...

Allen,

Thank you for the excellent information. It is a huge help.

I'm looking at your structure and wondering how I might be able to apply
it
to what I'm attempting. My biggest hurdle is this:

The main user (school secretary) as been using an access database she
built.
It is not relational. I'm going to build a new one from scratch but I
would
at least like to follow some of the data entry logic she uses when
entering
a new record. In other words, I would like the interface to (as close as possible) duplicate how she processes a new application.

First, she enters an "Inquiry". Her current form requires her to enter
both
parents first on the top of her form with contact info, then the
prospective
student underneath on the same form. Using your logic and database
structure, how could I create an Inquiry form that allowed me to list the student with BOTH parents on the same form? The contact info only needs
to
be entered once on the Inquiry form . Even if the parents do not live
together, the secretary only uses whatever address the student lives at.
I
don't think she even collects contact info currently on the other parent
if
they live apart, but it is something they would like to start doing. I
know
a subform would be the option. Would I make the form where the parents
are
entered, the subform of the student? Then have contact info listed with
the
student?

Thanks again for your invaluable help. I at least am starting to see the right direction I should take.

William


Primarily, the school Secretary will enter an "Inquiry" first. On this
form, she will
"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:42******** **************@ per-qv1-newsreader-01.iinet.net.au ...

William, as a starting point, see:
People in households and companies
at:
http://members.iinet.net.au/~allenbrowne/AppHuman.html

Human relationships are some of the hardest things to model with simple
flexibility. The article suggests that everyone (parents, students,
teachers, alumni) goes into one table, and you can then creating grouping

of

people such as households so you can track who belongs to whom.

It contains a basic sample database that illustrates the idea. One of
benefits of that simple design is that it's very easy to build on. For
example, since all the people are in one table, there is no drama

regardless

of whether it comes from a child, parent, or teacher.

HTH
"William Wisnieski" <wi************ ***@verizon.net > wrote in message
news:WCSMd.1003 0$g16.5282@trnd ny08...
> Hello Everyone,
>
> I'm really stuck on how to design this application, so I thought I'd
> see
> if
> anyone had any general ideas on how to proceed. I'd say I'm an
> intermediate
> level Access developer.
>
> I volunteered to help my kid's school (a small non-profit) with a
> tremendous
> need they have for a complete student administration database. I've
> developed fairly complex databases before but this one is very unique
> in
> how
> it must be designed.
>
> The challenge is this. A record must be created that contains

information

> about the student and both parents (tracking begins with an inquiry,

then

> admissions, then enrollment). Then, the database must contain some

sort

> of
> master table or index of every individual so an administrator can
> search
> for, say one parent because that parent may become an individual

donor to

> the school. So even though the parents names were entered as part of

the

> child's record, there must be a way to look up the parent later and
> have
> the
> parent's info displayed. That goes for Staff and Alumni. I know

I'll > need
> to build a table that will contain how people are related and
> affiliated
> to
> eachother and the school, but the part that is throwing me is how to
> get
> several names from one form/record into a master table or index so that > each
> individual then has their own ID. My form might look like this:
>
>
> Child Record 01
>
> Parent 1 Jane Doe
> Parent 2 John Doe
> Child 1 Suzy Doe
>
> =====>
>
> Master Table
>
> Jane Doe 001
> John Doe 002
> Suzy Doe 003
>
> There are plenty of siblings at the school. Not sure how to work
> around
> that either.
>
> Any design ideas to get me started would be greatly appreciated.
>
> Thanks for your help,
>
> William

[Allen Browne]

You could use the AfterInsert event procedure of each form to call a
procedure that checks that all 3 are now present (Mum, Dad, and Child). If
so, you could execute an Append query statement to create the desired
record(s) in the other table:
dbEngine(0)(0). Execute "INSERT INTO ...

To help you get the statement right if you are not used to SQL:
- Mock up a query using anyone as the Mum, Dad, and Child.
- Change it to an Append query (Query menu).
- Swith to SQL View (View menu).
Copy the example you see there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"William Wisnieski" <wi************ ***@verizon.net > wrote in message
news:tA5Od.3284 4$8a6.31059@trn dny09...

Thanks again Allen. I'm plugging away at this thing this morning and I
have
another question.

Is there anyway to add code to the inquiry form to automatically create a
new group after an inquiry is entered? I'm specifically wondering if a
new
group can be created for the three family members (mom, dad, child) after
the user enters new inquiry data and the entries are appended to the
client
table.

Thanks,

William
"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:42******** **************@ per-qv1-newsreader-01.iinet.net.au ...

Once you get a good data structure, you can interface it almost any way

that

suits the user. If you really want to emulate what she currently has, you
could use an unbound main form as a holder for 3 subforms:
- Dad
- Mum
- Child
She enters both parents, and then the child.
The entries are all saved into the same Client table.
In the AfterInsert event of the subform, you could create/append to the
entries in the other table(s) that define the family household.
"William Wisnieski" <wi************ ***@verizon.net > wrote in message
news:ieqNd.5960 $sR5.2518@trndn y05...

> Allen,
>
> Thank you for the excellent information. It is a huge help.
>
> I'm looking at your structure and wondering how I might be able to
> apply
> it
> to what I'm attempting. My biggest hurdle is this:
>
> The main user (school secretary) as been using an access database she
> built.
> It is not relational. I'm going to build a new one from scratch but I
> would
> at least like to follow some of the data entry logic she uses when
> entering
> a new record. In other words, I would like the interface to (as close as > possible) duplicate how she processes a new application.
>
> First, she enters an "Inquiry". Her current form requires her to enter
> both
> parents first on the top of her form with contact info, then the
> prospective
> student underneath on the same form. Using your logic and database
> structure, how could I create an Inquiry form that allowed me to list the > student with BOTH parents on the same form? The contact info only
> needs
> to
> be entered once on the Inquiry form . Even if the parents do not live
> together, the secretary only uses whatever address the student lives
> at.
> I
> don't think she even collects contact info currently on the other
> parent
> if
> they live apart, but it is something they would like to start doing. I
> know
> a subform would be the option. Would I make the form where the parents
> are
> entered, the subform of the student? Then have contact info listed
> with
> the
> student?
>
> Thanks again for your invaluable help. I at least am starting to see the > right direction I should take.
>
> William
>
>
>
>
> Primarily, the school Secretary will enter an "Inquiry" first. On this
> form, she will
> "Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
> news:42******** **************@ per-qv1-newsreader-01.iinet.net.au ...
>> William, as a starting point, see:
>> People in households and companies
>> at:
>> http://members.iinet.net.au/~allenbrowne/AppHuman.html
>>
>> Human relationships are some of the hardest things to model with
>> simple
>> flexibility. The article suggests that everyone (parents, students,
>> teachers, alumni) goes into one table, and you can then creating grouping > of
>> people such as households so you can track who belongs to whom.
>>
>> It contains a basic sample database that illustrates the idea. One of
>> benefits of that simple design is that it's very easy to build on. For
>> example, since all the people are in one table, there is no drama
> regardless
>> of whether it comes from a child, parent, or teacher.
>>
>> HTH
>>
>>
>> "William Wisnieski" <wi************ ***@verizon.net > wrote in message
>> news:WCSMd.1003 0$g16.5282@trnd ny08...
>> > Hello Everyone,
>> >
>> > I'm really stuck on how to design this application, so I thought I'd
>> > see
>> > if
>> > anyone had any general ideas on how to proceed. I'd say I'm an
>> > intermediate
>> > level Access developer.
>> >
>> > I volunteered to help my kid's school (a small non-profit) with a
>> > tremendous
>> > need they have for a complete student administration database. I've
>> > developed fairly complex databases before but this one is very
>> > unique
>> > in
>> > how
>> > it must be designed.
>> >
>> > The challenge is this. A record must be created that contains
> information
>> > about the student and both parents (tracking begins with an inquiry,
> then
>> > admissions, then enrollment). Then, the database must contain some
> sort
>> > of
>> > master table or index of every individual so an administrator can
>> > search
>> > for, say one parent because that parent may become an individual donor > to
>> > the school. So even though the parents names were entered as part
>> > of
> the
>> > child's record, there must be a way to look up the parent later and
>> > have
>> > the
>> > parent's info displayed. That goes for Staff and Alumni. I know I'll >> > need
>> > to build a table that will contain how people are related and
>> > affiliated
>> > to
>> > eachother and the school, but the part that is throwing me is how to
>> > get
>> > several names from one form/record into a master table or index so that >> > each
>> > individual then has their own ID. My form might look like this:
>> >
>> >
>> > Child Record 01
>> >
>> > Parent 1 Jane Doe
>> > Parent 2 John Doe
>> > Child 1 Suzy Doe
>> >
>> > =====>
>> >
>> > Master Table
>> >
>> > Jane Doe 001
>> > John Doe 002
>> > Suzy Doe 003
>> >
>> > There are plenty of siblings at the school. Not sure how to work
>> > around
>> > that either.
>> >
>> > Any design ideas to get me started would be greatly appreciated.
>> >
>> > Thanks for your help,
>> >
>> > William