I have a case where we have a company preference table where preferences are set and can be overridden by users. Preferences can be added to the table and the users should inherit them and can override them if they wish to do so by adding a record in the user 'UserBeer' table.

Beer (Table) BeerId, BeerName, Rating

UserBeer (Table) UserId, BeerId, Rating

I'm reading through the Inheritance mapping section of the docs and am trying to figure out if this is something that would produce a result similar to the following...

Select b.BeerId, b.BeerName, IsNull(ub.Rating, b.Rating) As Rating From Beer b Left Join UserBeer ub On b.BeerId = ub.BeerId

If there isn't a UserBeer record for a particular user and beer then the Rating will be null. In this case use the value from the Beer table for that particular beer.

I wish there was really a beer table

I'm afraid I didn't catch your question.

But I don't feel there should be an inheritance relation between Beer & UserBeer tables. I think Beer & User are having a m:n referential relation through UserBeer.

To have an Inheritance between SuperTable & SubTable, The PK of SubTable should be the FK to the SuperTable.

And I guess this is not applicable in your case.

Walaa wrote:

I'm afraid I didn't catch your question.

But I don't feel there should be an inheritance relation between Beer & UserBeer tables. I think Beer & User are having a m:n referential relation through UserBeer.

To have an Inheritance between SuperTable & SubTable, The PK of SubTable should be the FK to the SuperTable.

And I guess this is not applicable in your case.

Thanks for the reply.

Right, it is an m:n relation between Beer and User and maybe it's more of a design issue with the tables but the idea is to have a main site preference table where new preferences are added and through an outer join between Beer and UserBeer the query will return all from Beer and null for Rating from UserBeer where there is no record for that user in UserBeer. Using IsNull(ub.Rating, b.Rating) will replace the null Rating values where there is no UserBeer record with the Rating value from Beer.

The above works using a stored procedure and allows the user to override the site level preferences if they choose to (by adding a record to UserBeer). If a new preference is added at the site level then the user automatically gets it but has the option to override it.

Is there an alternate method that would return table entities rather than a DataTable from a stored procedure call? I haven't used the TypedView yet but that looks like it might be good option.

Is there an alternate method that would return table entities rather than a DataTable from a stored procedure call? I haven't used the TypedView yet but that looks like it might be good option.

Not at the moment, and TypedView will return a dataTable But you can create a database view and map it to an Entity rather than a Typed View.

Walaa wrote:

Is there an alternate method that would return table entities rather than a DataTable from a stored procedure call? I haven't used the TypedView yet but that looks like it might be good option.

Not at the moment, and TypedView will return a dataTable But you can create a database view and map it to an Entity rather than a Typed View.

Walaa,

I appreciate the help and advice. I'll look into both options since either should give me something strongly typed to work with.

samAdams wrote:

I wish there was really a beer table

I tried to read your question but each time I read the word beer, Homer Simpson popped into my head saying, "Mmmm, beer...."