Multiple Primary Keys

Posts   
 
    
mattsmith321 avatar
Posts: 146
Joined: 04-Oct-2004
# Posted on: 18-Oct-2004 20:48:24   

Hi all!

I've got two tables: Members and Events. Multiple members can 'belong' to an event so I have a EventMembers table that is two columns: EventID and MemberID.

When I am adding an event, I need to add any associated members to the EventMembers table. In the help file, there is an example that demonstrates adding related entities but that example appears to deal with tables with a single primary key. So, can I do what I need to do with the combination EventID+MemberID primary key, or should I add a new primary key column and create a unique constraint to enforce the relationships?

Any help for outlining approaches on this would be great.

Matt

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 19-Oct-2004 15:02:31   

mattsmith321 wrote:

Hi all! I've got two tables: Members and Events. Multiple members can 'belong' to an event so I have a EventMembers table that is two columns: EventID and MemberID.

When I am adding an event, I need to add any associated members to the EventMembers table. In the help file, there is an example that demonstrates adding related entities but that example appears to deal with tables with a single primary key. So, can I do what I need to do with the combination EventID+MemberID primary key, or should I add a new primary key column and create a unique constraint to enforce the relationships?

You can just add a new EventMemberEntity , set its 2 pk fields to a value and save it. You can also do:

EventMemberEntity newEventMember = new EventMemberEntity (); newEventMember.Event = newEvent; newEventMember.Member = newMember;

// and in the end, just save newEvent recursively.

Frans Bouma | Lead developer LLBLGen Pro
Posts: 112
Joined: 09-Aug-2004
# Posted on: 19-Oct-2004 15:03:50   

This is how I would do it



BL.EntityClasses.EventMembers em = new BL.EntityClasses.EventMembers(iEventID, iMemberID);

if (em.IsNew){
  em.EventID = iEventID;
  em.MemberID = iMemberID;
  em.Save;
}



That might work

mattsmith321 avatar
Posts: 146
Joined: 04-Oct-2004
# Posted on: 19-Oct-2004 18:23:42   

mattsmith321 wrote:

Hi all!

I've got two tables: Members and Events. Multiple members can 'belong' to an event so I have a EventMembers table that is two columns: EventID and MemberID.

When I am adding an event, I need to add any associated members to the EventMembers table. In the help file, there is an example that demonstrates adding related entities but that example appears to deal with tables with a single primary key. So, can I do what I need to do with the combination EventID+MemberID primary key, or should I add a new primary key column and create a unique constraint to enforce the relationships?

Any help for outlining approaches on this would be great.

Matt

I need to restate my question because I think I left out some critical bits of information which caused the wrong question to be answered.

First, I'm using the Adapter pattern and it is for a web app.

Second, the specific scenario that I am trying to address is when saving an Event. The Event form has a list of available Members that can belong to the Event. When creating an Event, the user would check the Members that need to belong to the Event. So, when saving an Event for the first time, I will have at least one (requirement), possibly more, Member that belongs to the Event. So, when creating an Event, I don't have an EventID to populate the EventMembers entity with prior to the transaction. I could save the Event, get the EventID and then save the EventMembers separately but as I mentioned, it appears from the help doco that you can save an Event that has EventMembers associated to it and it will save all of it for you. So, my question is: will it be able to populate the correct EventID into the EventMembers?

Follow-up question: Saving a new Event is relatively straight forward since you know that you are saving a new Event and whatever new EventMembers were chosen. However, what is the best strategy for saving changes to an Event where the EventMembers have been checked (or unchecked) by the user? Do I query for all associated EventMembers, delete them and then add back the chosen EventMembers? Or do I query the list of EventMembers and then iterate through the list comparing it to the latest selected members (which may or may not have been changed, or new members selected, or a member dropped from the event) and add a collection of EventMembers to Event that has the various states selected for Delete this association, Add this association, etc.

I'm just looking to find out how much LLBL Gen Pro can handle because of built-in functionality versus what I need to do custom. And whether things would be easier with a different primary key on the EventMembers table versus a compound primary key with EventID and MemberID.

Thanks for any help you can provide and if you need additional clarification, please let me know.

Matt

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 20-Oct-2004 09:50:21   

mattsmith321 wrote:

First, I'm using the Adapter pattern and it is for a web app.

Second, the specific scenario that I am trying to address is when saving an Event. The Event form has a list of available Members that can belong to the Event. When creating an Event, the user would check the Members that need to belong to the Event. So, when saving an Event for the first time, I will have at least one (requirement), possibly more, Member that belongs to the Event. So, when creating an Event, I don't have an EventID to populate the EventMembers entity with prior to the transaction. I could save the Event, get the EventID and then save the EventMembers separately but as I mentioned, it appears from the help doco that you can save an Event that has EventMembers associated to it and it will save all of it for you. So, my question is: will it be able to populate the correct EventID into the EventMembers?

Yes, it will take care of this. simple_smile It will save the entities in the correct order, so the PK side of a relation is saved first, then the FK is synced with the PK and then the FK side is saved. So if you have an Event entity, an EventMember entity and a Member entity you do this: eventMemberEntity.Event = eventEntity; eventMemberEntity.Member = memberEntity;

these 2 assignments set up synchronisation information behind the scenes. So when each one of these 3 entities is saved recursively, the graph will be saved in the correct order and all FK's will get the values from the PK's, even if these are autonumbers /identity fields simple_smile

Follow-up question: Saving a new Event is relatively straight forward since you know that you are saving a new Event and whatever new EventMembers were chosen. However, what is the best strategy for saving changes to an Event where the EventMembers have been checked (or unchecked) by the user? Do I query for all associated EventMembers, delete them and then add back the chosen EventMembers? Or do I query the list of EventMembers and then iterate through the list comparing it to the latest selected members (which may or may not have been changed, or new members selected, or a member dropped from the event) and add a collection of EventMembers to Event that has the various states selected for Delete this association, Add this association, etc.

That's a question that every developer has to face once or twice eventually simple_smile . In our CMS I had to fix this for the role-right management for example: a role gets a new right/loses rights, which records to update/delete? I ended up with: deleting all rights for that role, then save the new ones, all in 1 transaction. It was the easiest to program, and the result was the same. simple_smile

Frans Bouma | Lead developer LLBLGen Pro
mattsmith321 avatar
Posts: 146
Joined: 04-Oct-2004
# Posted on: 22-Oct-2004 05:30:58   

Otis wrote:

Yes, it will take care of this. simple_smile It will save the entities in the correct order, so the PK side of a relation is saved first, then the FK is synced with the PK and then the FK side is saved. So if you have an Event entity, an EventMember entity and a Member entity you do this: eventMemberEntity.Event = eventEntity; eventMemberEntity.Member = memberEntity;

these 2 assignments set up synchronisation information behind the scenes. So when each one of these 3 entities is saved recursively, the graph will be saved in the correct order and all FK's will get the values from the PK's, even if these are autonumbers /identity fields simple_smile

Very nice! It worked like a charm on the very first try!

Otis wrote:

That's a question that every developer has to face once or twice eventually simple_smile . In our CMS I had to fix this for the role-right management for example: a role gets a new right/loses rights, which records to update/delete? I ended up with: deleting all rights for that role, then save the new ones, all in 1 transaction. It was the easiest to program, and the result was the same. simple_smile

I think I will go with deleting all the existing entries first. If I had more data other than the two FK values in the table then I might do it differently but deleting first works for me right now.

idrees
User
Posts: 19
Joined: 01-Feb-2007
# Posted on: 18-Jul-2007 05:10:20   

Hi All

Just on the same topic.

No problem is storing and also retrieving when both primary keys are known. But what if only one of the primary key is known and I want retrieve a collection of all records.

Any suggestion is much appreciated.

Thanks Idrees

idrees
User
Posts: 19
Joined: 01-Feb-2007
# Posted on: 18-Jul-2007 05:50:33   

I sorted it out myself. I used GetMultiAsDataTable from DAO classes to get a DataTable and then iterate through it.

Thanks anyway.

Idrees