Relationship help

[ABreeze]

Can somebody help me straighten out my relationships. I have a Contact table with contactID as primary key. Each contact can register as a student, instructor, producer, retailer or a combination of these. I want to add check boxes on a subform on the main Contact form and have the user select from the registration options. I have a table to store the information for each of the options, ie. an instructor table. I quess my question is do I need a bridging table with contactID and registrationID. If so does the Registration table need other fields besides registration Id and Registration Description and how do I store the check box info. I am so confused. I've designed several databases and can't believe that something that should be so simple is dragging me down. I'd really appreciate any suggestions.

 

[BLeslie88]

Well here's my view...

You have the ContactID. This need to be stored in the other table as a reference.... When reporting or querying later the relationship to the contactID will give you all the info on the contact.

tblContact
fldContactID
fldContactName
fldContacPhoneNo
fldContactAddress
fldContactAge
so fourth......

tblRegistration
fldRegistrationId
fldContactID
fldRegistrationDescr

One-Many relationship here meaning that there may be many ContactID's = 11, which may be Joe Black, in your Registration table. Alas in your Contact table their should only be one ContactID = 11 which would have all the info on Joe Black. You could also make the field ContactID required in tblRegistration so that you will always have the reference. I usually enforce the relationship myself...

Hope this help....

Regards,

 

[Fizzio]

You are right to use a link table with the ContactID and RegistrationID in it and make a multifield primaryKey to avoid 1 contact registering for the same type more than once

ie
tblContacts
ContactID (PK)
etc

tblRegistrationTypes
RegistrationTypeID (PK)
RegistrationType

tblContactRegistrations
ContactID (PK)
RegistrationTypeID (PK)

The difficulty is how to display the info as checkboxes as the link table will have a separate record for each registration.

 

[ABreeze]

I have these three tables already set up. I knew basically how to handle these. My main problem is the linking tables for each of the registration options. For example, my Instructor table also has contactId for the primary key. Will this link to the main contact table or to the contactId field in the Registration bridging table.
Also, my registration tables (tblInstructor), (tblStudent), etc each have several fields unique to that option and I'd like to have the user select the registration options that apply and then be presented with the fields from each table that are pertinent to their selections.


Thanks for the help. It is greatly appreciated.