Primary Keys

[sir_dan_mitchell]

Very Simple Question

hi guys,

Really sorry to ask this, its been a while since ive used access.

I have a very simple idea and i know its a simple answer, but i just cant remember what to do!

I have 3 tables - members, arrangements, venues!

I only want one member to select the same venue once, if you get me e.g. Mr Doe - shopping, if that was done once I wouldnt want to be able to put it in twice.

I have primary keys for them all and 2 foreign keys in the arrangements table. I just cant figure out what I have to do to prevent this!?

Please help!

Dan

 

[Len Boorman]

How about a table with Member and Venue columns with both columns as primary key.

Combined entries must be unique

Len B

 

[sir_dan_mitchell]

hey,

I have an arrangements table where its supposed to take all the arranged people and set them to a venue.

I have Arrangements_ID as the primary key and then Member_ID and Venue_ID as foreign keys. And yet I can still put for example John Doe on the same venue twice! I think its a problem with relationships, but i dont know wot to do.


Can you help??

Thanks Dan

 

[Len Boorman]

Alternatively you could set up an index. This would mean you not having to tweak your relationships

Create a Unique Index Member ID and Venue_ID.

What purpose does the Arrangements_ID pk serve. Could you not use Menber_ID and Venue_ID as pk and they can still serve as fk's

Any way would seem to give you a solution

len b

 

[sir_dan_mitchell]

i take it you mean deleting arrangments ID.

I still want say one person to be placed on many different venues and one venue to have many different people on it. Just not the same person going to the same venue twice.

Would this work if i JUST had primary keys of members and venues combined in the same field?

Isnt it better normalised is i use another primary key like the arrangements one and that way it would work with some reconfigurement of the relationships?

Dan

 

[Len Boorman]

Hi Dan Had a problem myself yesterday as well.

No doubt you will note the comment from Pat re keys but in reply to your pm:

Having the two key primary key would allow

A A
A B
A C
B A
B B
B C

but would not allow any of those combinations to be duplicated.

Pat's comment about primary keys are very relevant. From your pm and the point re normalisation. Does Arrangement_ID actually convey any information about the relation?. Do you need it at this time for a child relationship ?.

My feelings would be that if it is not needed then remove it. It is somethingless to manage.

Len B

I know there are many opinions on pk's.