Hello, I am a newbie with LLBLGen Pro and have a couple questions. I am using SQL Server 2005 and the Adapter scenario in 1.0.2005.1. With that said...

In designing a new db, I have the following schema:

** ->Object (ObjectGUID PK) ---> Person (PersonGUID PK & is also a FK to ObjectGUID) ------> Employee (EmployeeGUID PK & is also a FK to PersonGUID)**

So far so good, in that an Employee inherits from Person which inherits from Object. I am using GUIDs as PKs and the PK of Employee is infact the same value as the PK for the parent Object (or Person). This way I can have an ObjectAddress table while still being able to relate an Employee to ObjectAddress since Employee.EmployeeGUID = ObjectAddress.ObjectGUID.

But the problem is that I dont want to leave the Employee (or any object) rows visible to all users. So I want to design a view ObjectView which will restrict all object rows based on the users visibility level (ex: company level, location level, etc).

1. How can a view participate in this relation in LLBLGen Pro? i.e. Can I replace Object being the parent of this structure to ObjectView being the parent instead. Though the FK constraints are defined on tables not views.

2. If I do the above, how can I accurately fetch an Employee by fetching an ObjectView first. ObjectView will not have fields such as LastName, FirstName etc so I cant do a search on ObjectView but rather have to search on Person or Employee and then I am not restricting my results using the ObjectView restrictions.

Does all this make sense?

Ok, nevermind the first question. It was simple enough to figure out how to create the relation manually for a view. My bad

I'm afraid I didn't understand what exactly are you trying to do.

But the problem is that I dont want to leave the Employee (or any object) rows visible to all users. So I want to design a view ObjectView which will restrict all object rows based on the users visibility level (ex: company level, location level, etc).

What did the ObjectView achieve?

First, everything inherits from Object (Customers, Employees, Orders, etc). The view (ObjectView) allows for partition of data. In a very simplified example, if you have 5 offices and each should have a separation where a user from one office cannot see another offices data you will access the view which restricts your resultset to your data.

Here is a simplified example to understand the concept:

Object table PK_Field PK, Office_Name

User table PK_Field PK, Office_Name

ObjectView

 select * from Object where office_name in (select office_name from udfGetUsersOffice())

I hope that makes sense, I have tried to make as simplified an example as possible.

I understand the reason for inheritance, but not with the view.

As I understand you have an Object Table already that everybody inherits from. And having just one View over this table will not hide data from some users and allow others to access it.

Maybe if you supplied me with the View creation script I can understand better.

Actually, the view definition was included in my last post, but here is it again.

create view ObjectView 
AS
select * from Object where office_name in (select dbo.udfGetUsersOffice())
GO

create function udfGetUsersOffice() returns varchar(25) as
begin
declare @username varchar(25)
select @username = system_user -- (system_user is a built-in system function, see BOL)

declare @office varchar(25)
select @office = office_name from user where username like @username

return @office
end

So one view is sufficient to return the appropriate data based on the SQL Server user. You dont need to hard-code a different view per office. Anyways, thats a different discussion...

How can I fetch an Employee doing an Inner Join with the parent ObjectView. Employee inherits Object & extends it with fields like LastName, FirstName. ObjectView narrows the scope of the data to your data (done at Object-level rather than have to create a view at each subentity level). So then, how can I pull an Employee using the name but then make sure the Object record for Employee is visible to me (Inner Join) so I know I didnt pull an employee i am not supposed to see.

Are you trying to make sure that you can define your hierarchy using the views instead of the tables? You should be able to create entities that are views and then define with columns are the primary key in their respective tables. Then define the relationship between the two and then Construct the Target-Per-Entity Hierarchies.

That sounds like what I am looking for - using TargetPerEntityHierarchy with views. If I define the PK of the underlying tables that make-up the view, I assume this entity would be updateable.

I'll give that a try, thanks.