Resolving many to many relationships

[Wull]

Hello all. Can someone tell me if I am doing this right. I have very limited knowledge on databases. I am putting something together for an institute that hosts various types of events, comedy, venue hire, music and exhibitions being examples of some of the things they do. I have created an entity that lists all these event types and also have a column with an autogenerated Primary key. I also have a Customer entity, one or more customers may have an interest in one or more events making a many to many relationship. My understanding of this is that I need to create a linking entity. I created an entity for this purpose called interst, it has an auto generated key for each record and 2 foreign keys. One being the PK of customer and the other being the PK from event.

Is this the correct way to do this?

Also, does the optional relationships ever get used in Access? i.e. one or more events MAY have the interest of more than one customer.

 

[maxmangion]

Hi Wull,

You are on the right track, but there is only one thing which i would change. In you junction table (the linking table), i would remove the auto generated field and use only the customerid and the eventid fields. Then you will set these two fields together as a primary key. In this manner you will be sure that no duplicate records are created.

In your current scenario you may have

autonumber customer event
1 joe comedy
2 joe comedy

In the above example you have a duplicate record for the same customer, but due to your autonumber it is not identified as duplicate.

 

[Wull]

That makes a lot of sense, thanks for pointing that out. I'll make that change when I get home from work.

I was getting a bit lost lastnight with that junction table. I want to create a form that allows staff to enter customer details, in that form I was looking to create a selection of check boxes so that multiple options may be ticked to select the customer's interest. After some research I believe I found the answer to be the Multivalued Lookup wizard I believe it is called.

If I include this in customer table and point it at the events in the event box, I can see the list of different events in the form, but trying to select any of them creates an error, (was at 2am when this error came up, didn't take a note of it and called it a night pretty much at that point).

Anyone have any tips on using this multi valued lookup tool?

 

[Wull]

I see what I was doing wrong now, I was trying to create a multi value look up from one side of the junction table to the other when I should have made it in the junction table.