We want to leverage a standard web application as the basis for user registration on our site. It has it's own DB structure, and we want to build on it's "user" table.

It is possible to map an entity across two DB's, so that we have this:

BaseWebApp UserTable OurWebApp EnhancedUserTable

Where EnhancedUserTable just references UserTable and adds data fields above and beyond what's provided in the base?

Ideally LLBLGen inheritance could be used to see this as one entity.

Any feedback or suggestions appreciated.

LTG

Through inheritance, this indeed works: supertype is mapped to table A, subtype is mapped to table B, and through inheritance inherits the mappings on table A.

Be aware that to make this work, a pk-pk 1:1 relation has to exist between entity mapped on A and entity mapped on B.

Just to confirm, this can work across 2 different databases in SQL Server?

Thanks for your reply -

LTG

Otis wrote:

Through inheritance, this indeed works: supertype is mapped to table A, subtype is mapped to table B, and through inheritance inherits the mappings on table A.

Be aware that to make this work, a pk-pk 1:1 relation has to exist between entity mapped on A and entity mapped on B.

hdgreetings.com wrote:

Just to confirm, this can work across 2 different databases in SQL Server?

Thanks for your reply -

LTG

Otis wrote:

Through inheritance, this indeed works: supertype is mapped to table A, subtype is mapped to table B, and through inheritance inherits the mappings on table A.

Be aware that to make this work, a pk-pk 1:1 relation has to exist between entity mapped on A and entity mapped on B.

Yes, just add both catalogs to your llblgen pro project. Sqlserver doesn't allow you to define FK constraints across catalogs but you can define the relation between the two entities in the designer, (between the two pk's) and then you can make one hte subtype of the other.

In regards to custom relations created in the LLBLGen designer, how does LLBLGen know if it's a 1-1 relation?

I created a custom relation like this recently expecting to get a one to one relation from it and ended up with a one to many.

tprohas wrote:

In regards to custom relations created in the LLBLGen designer, how does LLBLGen know if it's a 1-1 relation?

Quoting the docs:

Physical representation in the data model and LLBLGen Pro entities The typical hierarchy mentioned above is realized in your datamodel with one table / view per entity. This means that for the hierarchy above you'll get an Employee table / view, a Manager table / view and a BoardMember table / view. The Employee table is the leading table, where you define the primary key for the entity to uniquely identify an entity instance in the database. As ID is a perfect candidate for this (1:1 relation between entity Employee and attribute) this will become the primary key. Manager and BoardMember get this same field, to identity the rows stored in these tables, though they're not new PK values, but have a foreign key constraint defined to the ID of the supertype, so Manager.ID has a foreign key constraint to Employee.ID and BoardMember has a foreign key constraint to Manager.ID. This way, referential integrity rules in the database make sure your data stored in the database is correct, also for derived entities.

And the important note:

Don't use surrogate keys on the subtype tables, it's important the PK of the subtype tables has the foreign key to the supertype's PK.

So, you must make your two entities like:

Supertype

SomeUniqueField (PK) SomeOtherField ...

SubType

SomeUniqueField (PK) SomeSpecificField ...

Then you will be able to make your 1:1 relation.

Thanks so much for the answer. I just realized I should have been more specific.

I understand that this works based on the FK relationships designed in the schema. If your mapping relationships across catalogs there won't be any FK to map and the LLBLGen designer won't know that it's a one to one relation. It then by default creates a one to many as it doesn't know any better.

Do I understand this correctly?

tprohas wrote:

Thanks so much for the answer. I just realized I should have been more specific.

I understand that this works based on the FK relationships designed in the schema. If your mapping relationships across catalogs there won't be any FK to map and the LLBLGen designer won't know that it's a one to one relation. It then by default creates a one to many as it doesn't know any better.

Do I understand this correctly?

It creates a 1:1 relation if: - the FK is the PK OR - the FK has a unique constraint.

if the FK is just a field, not the PK and doesn't have a unique constraint, it creates a 1:n relation. So if you could check if this is the case?