I was trying to create a referentially safe multi-tenant db from an existing db by adding a second primary key column to my existing HiLo generated ID primary key column.
I am sorry if this is answered elsewhere -- I have tried to search for a solution/recommendation for this multiple place. I have seen good discussions about adding the Tenant ID and understand concerns about it. But that ship has sailed, and I must implement shared tables isolated by TenantID. By adding it to the primary key, I resolve some of the security concerns. Query length might become a problem with the extra join criterion on EACH join (argh), but I can live with what I have seen so far. Existing table: Create Table Lookup ( ID long NOT NULL PRIMARY KEY, Name nvarchar(100) NOT NULL, Description nvarchar(1000) NOT NULL ) Create Table UsesLookup ( ID long NOT NULL PRIMARY KEY, LookupID long NOT NULL, -- foreign key back to Lookup table Value long NOT NULL) New tables: Create Table Lookup ( ID long NOT NULL, TenantID long NOT NULL, Name nvarchar(100) NOT NULL, Description nvarchar(1000) NOT NULL ) ALTER TABLE [dbo].[Lookup] ADD CONSTRAINT PK_Lookup PRIMARY KEY NONCLUSTERED ([TenantID], [ID]) Create Table UsesLookup ( ID long NOT NULL PRIMARY KEY, TenantID long NOT NULL PRIMARY KEY, LookupID long NOT NULL, -- foreign key back to Lookup table Value long NOT NULL) ALTER TABLE [dbo].[UsesLookup] ADD CONSTRAINT PK_UsesLookup PRIMARY KEY NONCLUSTERED ([TenantID], [ID]) ALTER TABLE dbo.UsesLookup ADD CONSTRAINT FK_UsesLookup_Lookup_Lookup FOREIGN KEY (TenantID, LookupID) REFERENCES dbo.Lookup(TenantID, ID) Using a composite-id, I lose the generator and some key optimizations that I would hope to keep. Does anyone have a solution for this, perhaps using a IUserType that supports 2 columns? Or some NHibernate feature I am missing? Thanks, Kyle -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
