More efficient way required.

  • Thread starter Thread starter Griff
  • Start date Start date
G

Griff

Hi

I need a very efficient solution to a problem that I am sure must be a
fairly common one.

I have a web application that is database driven.

The behaviour of the web site is goverened by various database settings, for
example font-type, font-size as well as access to functionality (this
function switched on/off) etc.

For sake of simplicity, let us assume that all this information is held in
three tables with almost identical schema. The first table contains all the
settings for the Company, the next contains any over-rides required by a
specific Department, and the third table contains all the over-rides
required by a specific User.

4 use-case scenarios:
+ User A uses the company default values; the specific values in the
Department and User tables are 'NULL'.
+ User B uses his Department values to over-ride the values in the Company
table (his user record has 'NULL'
+ User C uses her own values to over-ride both the Company and the
Department values.
+ User D uses his own values to over-ride the Company values; Department
record contains 'NULL'

So, the scenario is that the UI demands to know (say) 10 parameters for a
particular user. Some values are therefore likely to come from all three
tables.

So, what is the fastest/most scalable way to achieve this (using VB.Net,
ADO.NET, SQL)? The information will be READ only at this stage. [I am
extremely new to .NET (VB6 back-ground) and THINK that I require a
DataReader object to be returned to the UI, though am happy to be advised on
this.]

In VB6, I guess that I would have done this by:
+ The UI calls the BL asking for a particular User's settings.
+ The BL calls the DL asking for a hierarachical recordset (data-shaped).
This contains the User record, the Department record and the Company record.
+ The BL then iterates through the User record - for every 'NULL' value it
looks in the Department record and (if that's NULL too) in the Company
record. It then returns to the UI the "merged" results.

Many thanks in advance!

Griff
 
So, what is the fastest/most scalable way to achieve this (using VB.Net,
ADO.NET, SQL)?
Click to expand...

Hi Griff,

Here's how it can be done in SQL. I don't know if this will be faster /
better / whatever than using VB or ADO.

Assuming that there might be no row at all for a specific user and/or
department in the appropriate tables, assuming that a row might have NULL
for some settings and non-NULL for other settings and assuming that the
table with company defaults will always hold exactly one row:

SELECT COALESCE(u.Setting1, d.Setting1, c.Setting1) AS Setting1,
COALESCE(u.Setting2, d.Setting2, c.Setting2) AS Setting2,
.....
FROM CompanyDefaults AS c
LEFT OUTER JOIN DepartmentDefaults AS d
ON d.DepartmentID = @DepartmentID
LEFT OUTER JOIN UserDefaults AS u
ON u.UserID = @UserID

If there will always be a row with default settings for each department
and/or each user (even if all settings are NULL), then change the LEFT
OUTER JOIN to an INNER JOIN. And make sure that the columns DepartmentID
and UserID are indexed.

Best, Hugo
 
Back
Top