Many one-to-many relatationships

[Dave]

Question. Can one table have a one-to-many relationship with 2
different tables? If so how do I set the parent-child relationships?
Are there other pitfalls I will run into?

Thanks in advance,

Dave

 

[Jason Lepack]

If you are discussing Relationships to tables then I have two
interpretations of your question, but either way you will have no
problem as long as your database is normalized.

First Interpretation: (One table with two "many" links)
tbl_student:
student_id (Unique) (1)
student_name

tbl_course:
course_id (Unique) (1)
course_room
course_name

tbl_enrollment: (the table in question)
student_id (Many)
course_id (Many)

Some people with more experience may be able to point out problems with
this, but I this is the best method of database design as far as I'm
concerned. Beign fully normalized, it reduces duplication of data,
therefore saving space. Queries can be used easily by joining the
tables to find any information you want. There are no drawbacks that I
am aware of.

Second Interpretation: (One table with two "one" links)

tbl_rental:
car_id (Many)
rent_date
return_date
renter_id

tbl_service:
car_id (Many)
service_date
service_location

tbl_car: (the table in question)
car_id (Unique)
car_make
car_model

There are no problems with this either. It's fully normalized and
having these two links won't harm the DB at all becuase the two tables
that it's joined to usually are not related, but even then it should
not be a problem (can't think of an example off the top of my head).

Now, if you're talking about adding Joins in the Query designer then
there are a lot of little pitfalls. I don't really have enough
experience to talk about. The good thing is that if you build a well
normalized relational database then you don't have to worry about these
issues very much.

Cheers,
Jason Lepack

 

[Dave]

Interpretation 2 is the one I'm after. How will this work in a form
with parent and child relationships if I have all three tables in the
form? Thanks for the response.

Dave

 

[John Vinson]

Question. Can one table have a one-to-many relationship with 2
different tables?

Sure. With two or with twenty if you need them.

If so how do I set the parent-child relationships?

Same way you do for the first one - use the Relationships Window and
drag the primary key field from the "one" table to the related field
in each of the "Many" tables.

Are there other pitfalls I will run into?

Sure... and some of them will have a Wumpus[1] which may devour you...
but that's life! <g>


John W. Vinson[MVP]

[1] http://en.wikipedia.org/wiki/Hunt_the_Wumpus

 

[Jason Lepack]

It all really depends what you are looking for.

Usually to access data like this I would use a form with a subform.
The parent table would be the recordsource for the main form and the
child table would be the recordsource for the subform. The two would
be linked on whatever field you chose to link the two tables on.

If you wanted to be able to access both child tables at the same time
you could use two subforms at the same time, using the same method as
above.

Cheers,
Jason Lepack