Database design problem.

Kai

Im trying to build a database which will control what links a user will see once he logs in. Now this isnt built yet but already im running into problems trying to see how im going to get it working.

I have 4 tables :
tblUser

---

id
username
site_id
level_id
password



tblSite

---

id
Site


tblLevel

---

id
Level


tblLinks

---

id
title
url



Now as i said these tables are not built yet so this layout can change. The Level table has the Work level the person is (Manager, team leader, worker ... etc) The site table records where they work (Ireland, England ...)

Now what i want to do is only allow people to see Links that are specified for their site and with their access level or lower. But i dont want to have to put in multiple entries in the link table for one link. But the problem is that Some people should be able to see all links from all sites.

I probably havent explained this as well as i should but can anyone give me an idea as to the right way to do this.

oscarBravo

Kai wrote:

Now what i want to do is only allow people to see Links that are specified for their site and with their access level or lower. But i dont want to have to put in multiple entries in the link table for one link. But the problem is that Some people should be able to see all links from all sites.

Is this a function of the level they're at, or of the individual? For example, if all managers can see all sites, then you can set up the query accordingly. If only some people should be able to see all the links, then you'll need another field in the users table to identify them.

When you're querying, you'll need to get the user data first. Based on either the user level or the flag as mentioned above, you're either selecting all records or selecting all records for the user's site with a level <= the user's level. This assumes you've set the levels up in a numeric format that you can compare easily like this. If the levels are actually names (like 'Manager' etc), then add a numeric level to the table that you can compare.

bonkey

Kai wrote:

Now what i want to do is only allow people to see Links that are specified for their site and with their access level or lower.

For a start, that will require that the Link table have a "minimum Access Level" field, or somesuch (it may be max level if you take 1 as the 'highest' access level, rather than the lowest)

But i dont want to have to put in multiple entries in the link table for one link. But the problem is that Some people should be able to see all links from all sites.

Shouldn't need to.

Join the tables using an inequality (I'll demonstrate) and you should have no problems...

SELECT <whatever>
FROM User
LEFT OUTER JOIN Link
	ON User.LevelId >= Link.MinimumLevelId
WHERE user.Name = <whatever> 

<edit>
Changed <= to >= cause I called the field in Link MinimumLevelId.
If higher levels of privs have lower IDs (e.g. admin is 1, Manager is 2, and so on) then it would be more appropriately:

...
	ON User.LevelId <= Link.MaxLevelId
...

jc

Kai

Shouldn't need to.

Join the tables using an inequality (I'll demonstrate) and you should have no problems...

Ok, i get the <= thing thats a good idea. Just to throw an extra spanner in the works. Most links should be viewable by all Sites, others by 1 0r 2 sites and others for only 1 site. How could i handle that baring in mind that the Access level also applies for each site.

Ill be putting a web frontend on it so i just want to add new links which i can specify which sites have access to it and what minimum leve of worker all in one line and the DB handles all the rest

Zaph0d

This is a common problem. You might like to make your system even better by adding a start and end time to your links so that they only become active on a go live date and they expire when they are no longer relevant.

Make a table like this

tblLinkPermission
-----------------
PermissionID int
LinkID int
SiteID int
UserID int
LevelID int
Everyone bit

This table specifies that a certain link is visible to a certain user or a certain site or a certain management level or visible to everyone.

Also add a column to your tblUser table to specify superusers who get to see all links.

tblUser
---------
id
username
site_id
level_id
password
superuser bit

Now you can add rows to tblLinkPermission to determine who gets to see which links. You need a query like this to decide whether a user is authorized to see a link.

--first get users whose site has permission
SELECT title,URL
FROM 	tblLinks l INNER JOIN tblLinkPermission lp
 		ON l.ID=lp.LinkID
	INNER JOIN tblSite s 
		ON s.ID=lp.SiteID
	INNER JOIN tblUser u
		ON u.site_id=s.ID
WHERE	u.ID = @myuserid

UNION
--then get users whose level has permission
SELECT title,URL
FROM 	tblLinks l INNER JOIN tblLinkPermission lp
 		ON l.ID=lp.LinkID
	INNER JOIN tblLevel le 
		ON le.ID=lp.LevelID
	INNER JOIN tblUser u
		ON u.level_id>=le.ID
WHERE	u.ID = @myuserid

UNION
--then get users who have individual permission
SELECT title,URL
FROM 	tblLinks l INNER JOIN tblLinkPermission lp
 		ON l.ID=lp.LinkID
WHERE	lp.UserID = @myuserid

UNION
--then get links that have the 'everyone' flag set
SELECT title,URL
FROM 	tblLinks l INNER JOIN tblLinkPermission lp
 		ON l.ID=lp.LinkID
WHERE	lp.everyone=1

UNION
--get all links if the user is a superuser
SELECT title,URL
FROM 	tblLinks  INNER JOIN tblLinkPermission lp
 		ON l.ID=lp.LinkID
	INNER JOIN tblUser u 
		ON u.ID=@myuserid AND u.Superuser=1

Kai

Holy crap.

right ill give that a go and let you know. think id better start reading that SQL server 2000 Programming book i bought so i can get my head round what im doing :-D

bonkey

Kai wrote:

Most links should be viewable by all Sites, others by 1 0r 2 sites and others for only 1 site. How could i handle that baring in mind that the Access level also applies for each site.

You'll need a LinkSite "connector" table.

A N-M relationship (which is exactly what you're describing - each site can potentially see many links, each link can potentially be seen by many sites) requires a "connector" table in standard relational database models.

If you have MSSQL 2000 loaded, ahve a look in the pubs database, at title, author and titleauthor. This is how you handle a book having one-or-many authors, and each author having one-or-many books.

What you're describing above as a "spanner in the works" is identical in concept. You have Links, Sites, and LinksForSites.

jc

bonkey

Zaph0d wrote:

You need a query like this to decide whether a user is authorized to see a link.

Any reason you UNIONed a load of seperate queries, rather than using a single query on that?

JC

Zaph0d

I find conditional joins hard to read and it's difficult to predict their execution plans.

The UNION approach is very wordy but simple to read and maintain.

Kai

Right guys thanks for the help so far. Im probably being a bit dense here but just to clarify :

When im inserting a new link, i need to update the link table and the permissions table ?

bonkey

Y'huh.

From what I can see of Zaph0d's design, you'll add one row to Link, and then one row for each Permission needed for this to LinkPermission (i.e. each user, site, etc. that gets permission).

Obviously, if you're saying "everyone can see this", you only add one permission, with the Everyone bit set.

jc