Hi LLBLGen folk,

I am very impressed by the LLBLGen Software, and am hoping that it can serve as the ORM layer for our current product. Specifically, I was very impressed to read that there is support for Composite PKs, in the following forum post by Frans Bouma: http://bytes.com/groups/net-c/593801-persistence-framework-support-composite-key#post2333510

Can a knowledgeable individual please clarify whether the following design will work with LLBLGen:

1. The same schema will exist on multiple systems, in a distributed fashion. a. Each system can create new rows and relationships b. At point in time, systems can synchronize in peer-to-peer fashion c. Relationships are preserved in the synchronizations

   1. To support the requirements of (1), each table has a Composite PK made up of a. A system_id which identifies the system where the row was created b. An id which is unique in the context of its given system_id c. Example:
      • system 1 creates a new User (system_id: 1, id: 1)
      • system 1 creates a new Job for this User (system_id: 1, id: 1, user_system_id: 1, user_id: 1)
      • system 2 creates a new User (system_id: 2, id: 1)
      • system 2 sync's with system 1, and has all rows
      • system 2 creates a new Job for the first User (system_id: 2, id:1, user_system_id: 1, user_id: 1)

As far as I know, this is the accepted way to use composite PK's for distributed db row creation. How is this supported in LLBLGen? How are the FK's configured?

Thanks

When you synchronize or share the data between different systems, would it retain its original SystemID values?

If so, then I see no problems what so ever in your scenario.

How are the FK's configured?

What do you mean by that?

Walaa wrote:

When you synchronize or share the data between different systems, would it retain its original SystemID values?

If so, then I see no problems what so ever in your scenario.

How are the FK's configured?

What do you mean by that?

Well, we found an earlier forum post here (http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=9564&HighLight=1) which left the impression with us that it was either not possible, or not easy, to configure a composite FK-PK relationship in LLBLGen.

In other words, if User has-one Job: - your Users table has a composite PK of (id, system_id) - your Jobs table then has a composite FK to Users of (user_id, user_system_id)

Is that clear? Furthermore for many-to-many relationships, you would have the same thing in the join tables of course: - your Users table has a composite PK of (id, system_id) - your Addresses table has a composite PK of (id, system_id) - your AddressUsers join table has either - a composite PK of (user_id, user_system_id, address_id, address_system_id) - or, two composite FKs of (user_id, user_system_id), (address_id, address_system_id) with a unique constraint on the pair of them

How does one configure these sorts of relationships in LLBLGen?

Thanks, -Marc

Well, we found an earlier forum post here (http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=9564&HighLight=1) which left the impression with us that it was either not possible, or not easy, to configure a composite FK-PK relationship in LLBLGen.

That old thread was speaking about foreign keys which reference not composite primary keys, but rather composite unique keys. And this is another story.

Is that clear? Furthermore for many-to-many relationships, you would have the same thing in the join tables of course: - your Users table has a composite PK of (id, system_id) - your Addresses table has a composite PK of (id, system_id) - your AddressUsers join table has either - a composite PK of (user_id, user_system_id, address_id, address_system_id) - or, two composite FKs of (user_id, user_system_id), (address_id, address_system_id) with a unique constraint on the pair of them

How does one configure these sorts of relationships in LLBLGen?

In your database design you just treat Composite PKs as Normal PKs, i.e. in the case of m:n relation you mentioned the middle table should have FKs to each of the Composite PKs of the referenced tables, whether or not these combined are used to be the Composite PK of the middle table or not.

Personally I prefer to use a surrogate Identity PK in the middle table to avoid having a "4- fields" composite PK. But anyway the FKs should be there to reference the User and Address tables.

And in LLBLGen Pro you shouldn't do any kind of configuration, the tool automatically discovers all the relations possible as long as they are based on PKs, whether or not the are composite.

Great! Thank you for this prompt response.

One more question - how can we configure LLBLGen on top of SqlCE 3.5 to create GUID primary keys for some tables?

Thanks, -Marc

One more question - how can we configure LLBLGen on top of SqlCE 3.5 to create GUID primary keys for some tables?

Do you want to create the GUIDs at server side using NEWID() or NewSequentialID()? Or do you want to create them at code?

Please check the following: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=11612 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=5971