junction table confusion - pleeeeease help!!!!!

[ngoz1]

Lets say i have three tables - a member table, a non
member table( storing all contact details of a member
or non member) and a profile table( storing all info
about events performed in, by a member or
nonmember,and their performance acts at that event
etc)

The member table has a one to many relationship with
the profile table.Same for the non member table.

The relationship is optional on both the member and
profile side of the relationship ie a member may have
one or many profiles. and a particular profile may
belong to only one member. I say 'may' b/c a profile
does not have to belong to a member,it could belong to
a non member. And also a member/nonmember does not
have to have a profile, as they may not have performed
in any events.

Now i read that a junction table is not only used for
many to many relationship but also when the many side
of a relationship is optional, this is b/c there is a
rule that says that a foriegn key(e.g the nonmember
and member foriegn keys in the profile table)must
never have a null value)

Based on this info', i made a junction table between
member and profile and one between nonmember and
profile called member/profile and nonmember/profile
respectively each having two primary keys

Okay you get the picture, so i'll continue with just
two tables(member and profile) to cut this story short


My questions are:
1) is it true, that a junction table must be used in
this type of situation or am i going down the wrong
path?

2) if it is true. then it means that i am left with
this ridiculous junction table with just two fields -
the 'member number' and the 'profile number'. My
confusion is - How does this table initially get
filled when a new member or profile record is
inputted?. surely i cant have a seperate form which
requests the user to fill in these details ? is'nt
that very user unfriendly?
yeah!!! they might know the membership number for a
particular person but they probably wont know the
profile number as it is an autonumber. And there is
really no field i can add this table to make it a more
memaningful table( ive checked). so what do you do
when you have a jxn table that is typically for
junction purposes alone?.

And correct me if im wrong but i am sure you cant
create a query between the member and profile table if
this junction table is empty

Engy

 

[r937]

1) yes, a junction table is required for a many-to-many relationship

2) yes, the junction table typically has just two fields -- together, they form the primary key, and individually, they are foreign keys back to their respective tables

other fields often used in junction tables include dateadded (when did this member get this profile), active (is this profile active for this member), etc.

the junction table gets filled in as follows:

when you are about to add a new member, you would typically choose her profile(s) from a (multiple) dropdown list, and then insert one or more rows into the junction table, one per selected profile, all with the same memberid

when you are about to add a new profile, chances are no member has it yet, so do nothing

on the other hand, if you are creating a new profile that you want every existing member to get, you run a oneshot query where you grab all the memberids from the member table and use those with the new profileid to insert into the junction table

finally, as to your question "i am sure you cant create a query between the member and profile table if this junction table is empty" -- of course you can create the query, it just won't return any results

rudy

http://rudy.ca/