I have a sync table that has the following fields:

TABLE NAME = SYNC sync_id created_dt created_by updated_dt updated_by is_dirty is_new is_deleted

The idea is that every table in my database would inherit from it since these are all common fields that i would need on tables anyway. So.... the sync table is the supertype and lets say CUSTOMER is the subtype.

when i create a new customer entity the sync_id is updated with the customer_id. This poses a problem since all tables will run over each others id's. The way i have them mapped is... the customer table has a foreign key constraint where the customer_id is the primary of the customer table but it is also a foreign key to the sync_id in the sync table.

This posting helped me get this far http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=4704&HighLight=1 but now I'm stuck.

So the question is.. how do i get unique id's in the sync table and still be able to match up the sync table rows with the rows from the other table joined to it ?

-Reginald

Use a has-a relationship between a table and SYNC instead of inheriting every table from SYNC (which is a is-a relationship).

So.... Instead of inheriting from sync table i should have a sync_id foreign key in every other table that creates a relationship between the two.

Example:

Table Customer

customer_id first_name last_name address_id company sync_id

Is this correct? If it is, that means that when creating a new customer entity i'd also have to create a new sync entity as well, right?

Is this correct? If it is, that means that when creating a new customer entity i'd also have to create a new sync entity as well, right?

Yes it's correct. And yes you will have to create the Sync entity and save it first then save the related Customer entity, this can be done in one go with LLBLGen Pro Entities, assign the Customer Entity to the related field in the Sync entity and just save the Sync Entity.