Need A Solution!

[Arpan]

Suppose an ASP application which retrieves data from a SQL Server
database table is accessed by 100 users. Each of the users have a
password. Assume that the DB table has 25 columns [Column1....Column25]
& the passwords of 3 users are 'A1', 'B2', 'C3' (excluding the quotes).

What the application should do is if the password is 'A1', then that
user should be displayed the records of the columns Column1, Column7,
Column16, Column20 & Column25 only. If the password is 'B2', then that
user should be displayed the records of the columns Column3, Column6,
Column 15 & Column 17. If the password is 'C3', then that user should
be displayed the records of the columns Column2, Column8, Column11,
Column17 & Column23, so on & so forth.

This can be implemented in this way:

----------------------------------------
If(strPassword="A1") Then
'retrieve Column1, Column7, Column16, Column20 & Column25
ElseIf(strPassword="B2") Then
'retrieve Column3, Column6, Column 15 & Column 17
ElseIf(strPassword="C3") Then
'retrieve Column2, Column8, Column11, Column17 & Column23
................
................
................
................
................
End If
----------------------------------------

This means that there will be 100 If....Else statements which obviously
will not only be an inefficient approach but also a highly cumbersome &
tedious one. How can this be implemented more efficiently?

Please note that all the passwords will be stored in a different DB
table.

Somewhat weird, I guess but that's what my client wants!!

Thanks,

Arpan

[Bob Lehmann]

>> How can this be implemented more efficiently?
Probably by normalizing your database, adding user / group tables and
assigning permissions to the users / groups.

Or, you could <barf>create 100 VIEWS for each of the users</barf> and do
your SELECT from the relevant VIEW.

Bob Lehmann

"Arpan" <ar******@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Suppose an ASP application which retrieves data from a SQL Server
database table is accessed by 100 users. Each of the users have a
password. Assume that the DB table has 25 columns [Column1....Column25]
& the passwords of 3 users are 'A1', 'B2', 'C3' (excluding the quotes).

What the application should do is if the password is 'A1', then that
user should be displayed the records of the columns Column1, Column7,
Column16, Column20 & Column25 only. If the password is 'B2', then that
user should be displayed the records of the columns Column3, Column6,
Column 15 & Column 17. If the password is 'C3', then that user should
be displayed the records of the columns Column2, Column8, Column11,
Column17 & Column23, so on & so forth.

This can be implemented in this way:

----------------------------------------
If(strPassword="A1") Then
'retrieve Column1, Column7, Column16, Column20 & Column25
ElseIf(strPassword="B2") Then
'retrieve Column3, Column6, Column 15 & Column 17
ElseIf(strPassword="C3") Then
'retrieve Column2, Column8, Column11, Column17 & Column23
...............
...............
...............
...............
...............
End If
----------------------------------------

This means that there will be 100 If....Else statements which obviously
will not only be an inefficient approach but also a highly cumbersome &
tedious one. How can this be implemented more efficiently?

Please note that all the passwords will be stored in a different DB
table.

Somewhat weird, I guess but that's what my client wants!!

Thanks,

Arpan

[Jeff Cochran]

On 3 Sep 2005 16:37:37 -0700, "Arpan" <ar******@hotmail.com> wrote:

Suppose an ASP application which retrieves data from a SQL Server
database table is accessed by 100 users. Each of the users have a
password. Assume that the DB table has 25 columns [Column1....Column25]
& the passwords of 3 users are 'A1', 'B2', 'C3' (excluding the quotes).
You need a lesson in database design. A user is an entity (table). A
password is an attribute (column) of that entity. As would be a
UserName and a UserID (Primary Key). You now have a table with a
UserID, UserName and Password column.
What the application should do is if the password is 'A1', then that
user should be displayed the records of the columns Column1, Column7,
Column16, Column20 & Column25 only. If the password is 'B2', then that
user should be displayed the records of the columns Column3, Column6,
Column 15 & Column 17. If the password is 'C3', then that user should
be displayed the records of the columns Column2, Column8, Column11,
Column17 & Column23, so on & so forth.
Same deal. You can bet each column is an attribute of something,
likely something different. Normalize your database, learn to use
authentication and learn to create an entity relationship diagram.
Your life will be a lot easier.
Please note that all the passwords will be stored in a different DB
table.
Please note that they shouldn't be.
Somewhat weird, I guess but that's what my client wants!!

Clients almost always want a working solution. Almost never would
thay decide on a seriously flawed implementation. If they did, and
forced it on a programmer, I'll bet 99% of programmers would turn down
the job.

Jeff

[Phill. W]

"Arpan" <ar******@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

What the application should do is if the password is 'A1', then that
user should be displayed the records of the columns Column1, Column7,
Column16, Column20 & Column25 only. If the password is 'B2', then that
user should be displayed the records of the columns Column3, Column6,
Column 15 & Column 17.

How about another table that links your "condition" against the columns
that the user should be shown, something like this

Condition Field
A1 1
A1 7
A1 16
A1 20
A1 25
B2 3
B2 6
.... etc. ...

OK, so you have to /retrieve/ the fields for a given "condition" and
/build/ them up into a list that you can use in subsequent SQL, but it
does give you complete flexibility if (or rather, when) the columns
required /change/ over time.

NB: I say "condition" because using a single password to access each
"set" of fields is probably /not/ a good idea - it's amazing how fast
such "restricted" information moves around ... ;-)

HTH,
Phill W.

[Arpan]

Thanks Bob, Jeff & Phill for your helpful suggestions.

Regards,

Arpan