Designing a Database

[radiaz via AccessMonster.com]

I need to create a database for school and I'd like to get some help from you
experts. I'm new to Access, but I'm learning.
So far I have put together three tables, but I'm not sure If I'm doing the
right thing.

tblParent
SocialSecurity PK Text (Link to tblChildren.SocialSecurity as a One to
Many)
FirstName Text
LastName Text
ReligionType Text (Combo box getting values from tblReligion)

tblChildren
ID PK Autonumber
SocialSecurity Foreign Key Text
FirstName Text
LastName Text
ReligionType Text (Combo box getting values from tblReligion)


tblReligion
ReligionType PK Text ( Link to tblChildren.ReligionType as a One to
Many. Also Link to
tblParent.
ReligionType as a One to Many) I don't know if I can do that.

Will this work? I want to be able to get this data; Parents information along
with whether or not they have children and if they do have children, I'd like
to get which religion they belong to. Some parents's religion differ from
their children's religion.
I know this is something simple, but I just want to make sure I'm on the
right track.
Thanks,

Rita

 

[Guest]

Many children still have two perents. You need to indicate both. Two ways
to do it.
First way --
Change tblChildren.SocialSecurity to Parent-1 Foreign Key Text. Add a
field Parent-1 Foreign Key Text.

Second way --
Use a junction table set as one-to-many from tblParent.SocialSecurity and
also
one-to-many from tblChildren.ID -- in this way a parent can have many
childrern and children many parents.

 

[Guest]

tblParent
SocialSecurity PK Text (Link to tblChildren.SocialSecurity as a One
to Many)
ParentFirstName Text
ParentLastName Text
ParentReligionTypeID Number (Link to tblReligion)

tblChildren
ChildrenID PK Autonumber
SocialSecurity Foreign Key Text
ChildFirstName Text
ChildLastName Text
ChildReligionTypeID Number (Link to tblReligion)

tblReligion
ReligionTypeID PK
ReligionText Text (Name of religion)

 

[radiaz via AccessMonster.com]

Thnaks so much for your answer. For this project only one person can only
have one or many children. I know what you mean, it's weird. There will not
be duplicates in tblParents. In this case, will my design work?
Again, i appreciate.
Rita

 

[Guest]

It should do it for you. Use a query with both tables joined on the
tblParent.SocialSecurity and tblChildren.SocialSecurity.
Use the query for your main form - Parents and for the subform - Children
also linked Master/Child on tblParent.SocialSecurity to
tblChildren.SocialSecurity.

 

[radiaz via AccessMonster.com]

Thanks Karl again for your answer. One quick question, will it still work if
I just eliminate the tblreligion and add a religion type field in each
tblParents and tblchildren table? I will only have two tables now. I seem to
be getting the information I need easier than when I had the tblreligion
table. It just a thought.
What do you think?
Will it still be normalized?

Thanks,

Rita

It should do it for you. Use a query with both tables joined on the
tblParent.SocialSecurity and tblChildren.SocialSecurity.
Use the query for your main form - Parents and for the subform - Children
also linked Master/Child on tblParent.SocialSecurity to
tblChildren.SocialSecurity.

Thnaks so much for your answer. For this project only one person can only
have one or many children. I know what you mean, it's weird. There will not

[quoted text clipped - 36 lines]

 

[Guest]

One quick question, will it still work if I just eliminate the tblreligion
and add a religion type field in each tblParents and tblchildren table?
Yes it will work but will be subject to errors in data entry.

Thanks Karl again for your answer. One quick question, will it still work if
I just eliminate the tblreligion and add a religion type field in each
tblParents and tblchildren table? I will only have two tables now. I seem to
be getting the information I need easier than when I had the tblreligion
table. It just a thought.
What do you think?
Will it still be normalized?

Thanks,

Rita

It should do it for you. Use a query with both tables joined on the
tblParent.SocialSecurity and tblChildren.SocialSecurity.
Use the query for your main form - Parents and for the subform - Children
also linked Master/Child on tblParent.SocialSecurity to
tblChildren.SocialSecurity.

Thnaks so much for your answer. For this project only one person can only
have one or many children. I know what you mean, it's weird. There will not

[quoted text clipped - 36 lines]

Rita

 

[Tim Ferguson]

One quick question, will it still work if
I just eliminate the tblreligion and add a religion type field in each
tblParents and tblchildren table? I will only have two tables now.

PMFJI: There is no prize for having a small number of tables -- usually
there's a penalty to pay in the end. It doesn't take long to type in and
maintain a small table of value that change very infrequently.

One disadvantage is the necessity to join tables whenever you want to
know what religion a person belongs to. With a sensible design, however,
it is possible to avoid this too, and still retain complete control over
the contents of the FK fields. Consider a table with a single Text(16)
field, which is also the primary key:

EnglishName
===========
Catholic
Protestant
Zain
Buddhist
Sunni
Hindu
etc
etc


and then the People (or Children, or GrownUps etc) tables like this:

ID SSN FName LName Religion
===== ------ -------- ------- ---------
1 90k89 Eric Idle Catholic
2 92b67 Simon Grimon Zain

etc; where the People.Religion field is defined as a FK referencing the
Religions.EnglishName field... This means you can't put in a misspelled
value or one that doesn't exist, but you can still get sufficient
information in the table to avoid having to make a join on a numeric
field.

You can also use the Religions table as the RowSource of a list box or
combo for the users to pick an appropriate value.

Hope that helps


Tim F

 

[radiaz via AccessMonster.com]

Tim, I really appreciate your response.
I actually ended up doing what you are saying. One problem I'm having when
"You can also use the Religions table as the RowSource of a list box or

combo for the users to pick an appropriate value" is that I get an error telling me "You can not add or change a record because a related is required in tblReligion.

tblReligion looks like

tblReligion
Catholic
Baptist
Atheist
etc..

It only has one field with the PK assign to it.
The Religion Type field that I have in the other tables, I made them into a
combo box, then selected table/query - tblReligion - column count = 1
It works when selecting the data but when I go to enter a new person, I get
that error.

Any ideas?

Thanks,

Rita

 

[Tim Ferguson]

"You can not add or change a record because a related
is required in tblReligion."

when I go to enter a
new person, I get that error.

You need to check exactly what is in the field in the person record.

Do you have a Default Value set? If so, it should be a valid value from
the tblReligions table.

Do you have Allow Zero Length Strings set?-- this will allow a value of
"" (which is not the same as null, although it's hard to see the
difference on screen) which is a legal string, but obviously not valid in
the tblReligion domain.

If neither of those help, you'll have to explain in a bit more detail
exactly what is happening to raise the error.

All the best


Tim F