not sure how to describe this question...

  • Thread starter Thread starter mattdaddym
  • Start date Start date
M

mattdaddym

Hi all,

I'm writing an application in asp .net and vb .net. The question I have
just verifies I need to take an official programming course instead of
this "learning on the fly" stuff :)

Here's the situation. I have a site that people will access from
different site codes (site code = building ID). Each user will have a
profile, and permissions to the system based on the profile. The user
currently can only makes changes to the database for the site
code(building) he/she resides in. I'd like to be able to add the
ability to make a user responsible for several sites. So, for now, I
have an sql database with a site_code column that holds the id of the
site for the user.

The issue I am having is how to manage multiple site codes for a user
when I don't know if he/she will be responsible for 1,2,3,4, or more
sites. Here is what makes sense to me, and I appreciate any input on
this.

I should be able to store the one or more site codes the user has
access to in one database column. Maybe delimited by spaces or commas
or I don't know.

When I load these site codes, should I use some kind of array or
collection? Now we are getting into the asp .net part. I need to store
this information in session so it is available across pages. Eitehr
way, when I get to a routine where I need to check the site codes a
user has permission to, I should be able to do something like:

For each site_code in <something>
'check whatever against the site code and determine what should be
done
Next

Am I making any sense? I am inexperienced with this kind of thing.
Thank you!
 
Why not have a table of sites with a ResponsiblePerson column?

SiteCode | ResponsiblePerson
------------|----------------------
NYNYC |Steven
NYLNG |Steven

Then just do a SELECT to grab them:

SELECT SiteCode FROM Sites WHERE ResponsiblePerson='Steven'

The way you suggested would work, but you'd need to split the site codes
using something like so...

dim Sites() as string
Sites=SiteCodes.Split(",")

Now you'd have an array of strings.

For I as Integer=0 to Sites.Length-1
SiteCode=Sites(I)
'Do somethign with SiteCode
Next
 
I frequently see people who try to put comma separated data into a
database field. I frequently advice them not to.

Put the information in a separate table, much like Terry Olsen
suggested, only use the id of the site and the person if possible.

If you put all sites for a person in a single field, that field is more
or less useless for database operations. If you for instance want to
list all the persons responsible for different sites, you have to write
a lot of code to look up the information. If you store the information
in a separate table, you just join the tables in a query, and you get
the information fast and simple.
 
Good idea but how do you handle multiple persons responsible for multiple
sites? When using delimiters in a table column, I have been able to conduct
searches using the "like" operator.
 
Hello (e-mail address removed),
All the other suggestions about database storage are good. A database model
that supports multiple people responsible for multiple entities is a fairly
simple construct.

An aspect that hasnt been addressed is what to do with the data. Newbies
will probably tell you to read the data from the DB on each page load. YUK.
Or they might say read it on the first page load and store the data in session
variable/s. Equally YUK. Look into Forms/Windows Authentication. I can't
remember off the top of my head what the object is, though I think it's Page.Identity.
You populate this at session start.. then you can figure out what the user
is allowed to do by asking Page.identity.InRole (or IsInRoll ).. anyhow..
you get the idea.

Take care,
-Boo
 
Dennis said:
Good idea but how do you handle multiple persons responsible for multiple
sites?
Click to expand...

Any data that you can put in a comma separated field, you can put in a
separate table, without losing any functionality.
When using delimiters in a table column, I have been able to conduct
searches using the "like" operator.
Click to expand...

That only works if no identifier can appear as part of another
identifier. If you for an example search for the id "12", you will also
find any id that contains the combination "12", like "512", "126",
"1128", and so on.


The idea that each field should only contain one piece of information is
nothing new or revolutionary. (It's even older than me. ;)

In normalization it's known as the first normal form. Any relational
database should preferably follow the first, second and third normal
forms. Possibly even the fourth, fifth and sixth normal forms also.

Almost everyone adversed in database design looking at your database
would advise you to normalize it.

http://en.wikipedia.org/wiki/Database_normalization
 
You all are awesome, and I am dumb. I bow before your greatness... :)
Yes, I should create a new table. I appreciate the response about what
to do with the data afterwards. It seems inefficient to call it from
the database each time, but it is difficult to write a standard routine
to store the data when you don't know how many sites a user is
responsible for.

I have not used the asp.net 2.0 roles yet, but I am familiar with them.
Maybe for each site the user has permission to then I put him in a role
which is titled the name of the site. When validation is performed,
then I check the site code on the form and verify the user is in that
role. Yeah, that's the ticket.

Thanks again. I'd be lost without you all...
 
When I use multiple data in a field, I use special character separators like
%512%612%12% then search for %12% or %512. Not pretty but it works for
simple applications that I write.
 
Supporting multiple people responsible for multiple in a database may be
simple to you but not to newbie's like myself. Do you have either a
reference to describe some techniques or an example? Thanks.
 
Dennis said:
Supporting multiple people responsible for multiple in a database may be
simple to you but not to newbie's like myself. Do you have either a
reference to describe some techniques or an example? Thanks.
Click to expand...

Here's an example on how you can store the data:

Persons (PersonId, Name)
1, 'John'
2, 'Peter'
3, 'Audrey'

Sites (SiteId, Name)
1, 'Info'
2, 'Statistics'
3, 'News'
4, 'Economy'

SiteAdmins (SiteId, PersonId)
1, 2
2, 1
3, 1
3, 2
3, 3
4, 3

This gives that:

John is admin for: Statistics and News.
Peter is admin for: Info and News.
Audrey is admin for: News and Economy.

Admins for Info is: John.
Admins for Statistics is: Peter.
Admins for News is: John, Peter and Audrey.
Admins for Economy is: Audrey.
 
You're right, it's simple (now that I have an example)..thanks a lot as I
have struggled with this for some time.
 
Back
Top