How many one to many relationships are too many?

[Debra Farnham]

WinXP and Office 2003

Hi all

I am at a crossroads with a database I have just begun designing. The
database will deal with children (students), their disabilities, their
medication, transporation, etc.

I have one main table entitled tblStudent which, among other fields
descriptive of the student, contains a unique field StudentID (a nine digit
number that will never change throughout the child's education),


Each child will have one or more disability
Each child will be on at least one medication
Each child will have contact information (it may be one parent, both parents
or a legal guardian)
Each child's educational records must be kept (and each child will always
have at least one record of some sort).
Each child will be be involved with one or more outside agency
Each child will be assigned to one or more therapists
Each child will have one or more educational assistants

At the very least, I can see seven different one to many relationships
between each child and the various details that must be stored.

So my question is: (As my subject reads) How many one to many relationships
are too many? or am I way off in trying to create a relationship between the
student ID in the Student table and all the other entities? I realize, of
couse, that I will require some junction tables to deal with the many to
many issues.

As always TIA for any assistance!

Debra

 

[John Vinson]

At the very least, I can see seven different one to many relationships
between each child and the various details that must be stored.

Perfectly normal.

So my question is: (As my subject reads) How many one to many relationships
are too many?

You should have as many relationships as are required by the nature of
your data. I've got at LEAST 250 relationships in one of my databases
(haven't counted lately).

or am I way off in trying to create a relationship between the
student ID in the Student table and all the other entities?

Almost certainly not *all* of them, but those entities which are
properly related one-to-many to the Student table.

I realize, of
couse, that I will require some junction tables to deal with the many to
many issues.

Probably lots: surely each Therapist will potentially deal with more
than one child; any given Medication may be taken by many children;
etc. etc. Of course each many to many relationship decomposes into two
one-to-many relationships.

But don't worry about "too many relationships" - it's not an issue.

John W. Vinson[MVP]

 

[Debra Farnham]

Thank you for your prompt response John.

I hate it when I start second guessing myself .

Debra

 

[Smartin]

WinXP and Office 2003

Hi all

I am at a crossroads with a database I have just begun designing. The
database will deal with children (students), their disabilities, their
medication, transporation, etc.

I have one main table entitled tblStudent which, among other fields
descriptive of the student, contains a unique field StudentID (a nine digit
number that will never change throughout the child's education),


Each child will have one or more disability
Each child will be on at least one medication
Each child will have contact information (it may be one parent, both parents
or a legal guardian)
Each child's educational records must be kept (and each child will always
have at least one record of some sort).
Each child will be be involved with one or more outside agency
Each child will be assigned to one or more therapists
Each child will have one or more educational assistants

At the very least, I can see seven different one to many relationships
between each child and the various details that must be stored.

So my question is: (As my subject reads) How many one to many relationships
are too many? or am I way off in trying to create a relationship between the
student ID in the Student table and all the other entities? I realize, of
couse, that I will require some junction tables to deal with the many to
many issues.

As always TIA for any assistance!

Debra

I would say it looks like you have given quite a bit of thought to how
to design your database, done a very good job at it, and you are well
within the abilities of Access.

I imagine those among the readership who have designed and implemented
Access DBs professionally can tell you they have far more than seven
joined tables.

Nice job!

 

[Graham Mandeno]

Hi Debra

I have never seen published the limit of the number of relationships you can
have in a database, but I suspect that the limit, if there is one, would be
upwards of the hundreds. So, you should have no problem at all

You should realise, though, that all of the relationships you have described
(except maybe the educational records) are many-to-many, not one-to-many.

For example, a child may have one or more disabilities, BUT ALSO a
disability may afflict more than one child.

Many-to-many relationships cannot exist between two tables. You need a
third table, commonly referred to as a "junction table", and you create two
one-to-many relationships - one between Table A and the junction table, and
one between Table B and the junction table.

For example:

Table: Students
StudentID (primary key)
FirstName
LastName
DateOfBirth
HomeAddress
.... other data relating to this student

Table: Disabilities
DisabilityID (PK - autonumber?)
DisabilityName
.... and general data about the disability

Junction table: StudentDisabilities
StudentID (foreign key to Students.StudentID)
DisabilityID (FK to Disabilities.DisabilityID)
.... any data relating to this particular student's disability

 

[Graham Mandeno]

DOH! I just reread your post and saw the last paragraph. So you knew about
junction tables all along.

Sorry about that - I certainly didn't intend to sound patronising
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Debra

I have never seen published the limit of the number of relationships you
can have in a database, but I suspect that the limit, if there is one,
would be upwards of the hundreds. So, you should have no problem at all


You should realise, though, that all of the relationships you have
described (except maybe the educational records) are many-to-many, not
one-to-many.

For example, a child may have one or more disabilities, BUT ALSO a
disability may afflict more than one child.

Many-to-many relationships cannot exist between two tables. You need a
third table, commonly referred to as a "junction table", and you create
two one-to-many relationships - one between Table A and the junction
table, and one between Table B and the junction table.

For example:

Table: Students
StudentID (primary key)
FirstName
LastName
DateOfBirth
HomeAddress
... other data relating to this student

Table: Disabilities
DisabilityID (PK - autonumber?)
DisabilityName
... and general data about the disability

Junction table: StudentDisabilities
StudentID (foreign key to Students.StudentID)
DisabilityID (FK to Disabilities.DisabilityID)
... any data relating to this particular student's disability

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

WinXP and Office 2003

Hi all

I am at a crossroads with a database I have just begun designing. The
database will deal with children (students), their disabilities, their
medication, transporation, etc.

I have one main table entitled tblStudent which, among other fields
descriptive of the student, contains a unique field StudentID (a nine
digit number that will never change throughout the child's education),


Each child will have one or more disability
Each child will be on at least one medication
Each child will have contact information (it may be one parent, both
parents or a legal guardian)
Each child's educational records must be kept (and each child will always
have at least one record of some sort).
Each child will be be involved with one or more outside agency
Each child will be assigned to one or more therapists
Each child will have one or more educational assistants

At the very least, I can see seven different one to many relationships
between each child and the various details that must be stored.

So my question is: (As my subject reads) How many one to many
relationships are too many? or am I way off in trying to create a
relationship between the student ID in the Student table and all the
other entities? I realize, of couse, that I will require some junction
tables to deal with the many to many issues.

As always TIA for any assistance!

Debra

 

[Debra Farnham]

No offence taken Graham

My original concern was the StudentID appearing in so many places but I
guess I'll not worry any longer.

Thanks again

Debra

DOH! I just reread your post and saw the last paragraph. So you knew
about junction tables all along.

Sorry about that - I certainly didn't intend to sound patronising
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Debra

I have never seen published the limit of the number of relationships you
can have in a database, but I suspect that the limit, if there is one,
would be upwards of the hundreds. So, you should have no problem at all


You should realise, though, that all of the relationships you have
described (except maybe the educational records) are many-to-many, not
one-to-many.

For example, a child may have one or more disabilities, BUT ALSO a
disability may afflict more than one child.

Many-to-many relationships cannot exist between two tables. You need a
third table, commonly referred to as a "junction table", and you create
two one-to-many relationships - one between Table A and the junction
table, and one between Table B and the junction table.

For example:

Table: Students
StudentID (primary key)
FirstName
LastName
DateOfBirth
HomeAddress
... other data relating to this student

Table: Disabilities
DisabilityID (PK - autonumber?)
DisabilityName
... and general data about the disability

Junction table: StudentDisabilities
StudentID (foreign key to Students.StudentID)
DisabilityID (FK to Disabilities.DisabilityID)
... any data relating to this particular student's disability

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

WinXP and Office 2003

Hi all

I am at a crossroads with a database I have just begun designing. The
database will deal with children (students), their disabilities, their
medication, transporation, etc.

I have one main table entitled tblStudent which, among other fields
descriptive of the student, contains a unique field StudentID (a nine
digit number that will never change throughout the child's education),


Each child will have one or more disability
Each child will be on at least one medication
Each child will have contact information (it may be one parent, both
parents or a legal guardian)
Each child's educational records must be kept (and each child will
always have at least one record of some sort).
Each child will be be involved with one or more outside agency
Each child will be assigned to one or more therapists
Each child will have one or more educational assistants

At the very least, I can see seven different one to many relationships
between each child and the various details that must be stored.

So my question is: (As my subject reads) How many one to many
relationships are too many? or am I way off in trying to create a
relationship between the student ID in the Student table and all the
other entities? I realize, of couse, that I will require some junction
tables to deal with the many to many issues.

As always TIA for any assistance!

Debra

 

[Graham Mandeno]

Hi Jamie

PS Missed opportunity: the title of this thread could have been, 'One
too many one to many?'

LOL!

"The operation failed. There are too many indexes on table 'Table0'."

I do know that there is a limit on the number of indexes in a single table -
I believe it's 32.

However, the strange thing is that you should not be creating any indexes on
Table0 with this code. I tried it myself (just with 8 fields for each of
Table1 and Table2) and then ran the following code:

Dim db As DAO.Database
Dim td As DAO.TableDef
Dim ix As DAO.Index
Set db = CurrentDb
Set td = db("Table0")
For Each ix In td.Indexes
Debug.Print ix.Name, ix.Fields(0).Name
Next

Sure enough, I got only one index listed - the PK on key_col.

Huh? How do I get rid of this table now...?

Delete the database in Windows Explorer?

I think this is what's known as a Catch-22