How should I structure this database . . . ?

[RottPaws]

I have to make a database to track/summarize information about open orders for a department. The database is primarily for a director who has 4 managers. It's being build in Access97.

I have criteria to extract data on the orders from another database. Managers are then supposed to go in each week and comment on problems with each of these orders or designate someone to do so.

Each manager is in charge of a number of departments. Each order has a department code. Each manager may designate one or more people to go in and make these status comments each week. The managers need to be able to selectively assign their designees to one, several, or all of their departments.

What I need is some type of structure to control what each user is able to see.

*The director should be able to see everything.
*Each manager should be able to see everything with a
department code that falls under them.
*Each designee should be able to see only orders with
department codes that have been assigned to.

Does anybody have any suggestions on how I should structure the database?

Thanks in advance!
_________
Rott Paws

 

[Oppenhiemer]

Hi

To be honest Rott Paws, I think there may be some confusion over visibility and structure. The Structure of the database reflect the logical relationships between the entities or tables (and their structure. )

Are you in fact concerned more about the visability (for practical or security reasons)? If so, then the relavent information can be surfaced out using the appropriate SQL statements (Using WHERE clauses.)

Opp.

 

[RottPaws]

Opp,

It's kinda a combination . . . . What I'm looking for are suggestions on how to structure the database to get the visibility/security control that I want.

I know I can limit the recordsets for forms, reports, etc. through queries (WHERE clause), but I need to store which department(s) a user is allowed to see so I can stick that into the queries as needed.

A given user may be granted acess to see anything with department code RA1, they may have access to RA1 and RA3, or they may have access to RA*.

I'm just trying to think of a practical way to store these in table(s) and fields so they can be retrieved as necessary and used in queries when forms and reports are opened.

Does that make more sense? Is there some alternative (better) way of doing this? _________
Rott Paws

 

[dickiebird]

Try using views :
eg
create view dept1view as
select blah bla from table1 table2 etc
where Dept = 1

then grant privileges to the view,to manager of Dept1 only
For reports , use the view - or create others
HTH ;-) Dickie Bird
db@dickiebird.freeserve.co.uk

 

[Oppenhiemer]

Yes - that would work for some databases DikieBird - but Access97 does not support views (as far as I know.)

I think I know what RottPaws wants, but its beyond the time I have to give the answer (I believe whats needed are table definitions and relationship links.)

Sorry I can help further..

Opp.

 

[RottPaws]

Thanks anyway for the suggestions.

I'll figure out some way to make it work. _________
Rott Paws

 

[DaveStL]

You're going to need several tables containing the possible relationships (visibilities) you want to establish. I threw something together to play around with and came up with the following tables:

Comments [OrdNum, Name, Comments]
Assignments [OrdNum, Name]
Orders [OrdNum, Dept]
Reporting [Name, ReportTo]
DeptCodes [Dept, Name]

From this I was able to create several queries that appeared to show only the records under the conditions you mentioned above. For example, to show records for any given department (RC5):

SELECT Comments.OrdNum, Comments.Comments, Comments.Name, Orders.Dept
FROM Comments LEFT JOIN Orders ON Comments.OrdNum = Orders.OrdNum
WHERE (((Orders.Dept)="RC5&quot)
ORDER BY Comments.OrdNum;

To show records from employees under any given supervisor (James):

SELECT Comments.OrdNum, Comments.Comments, Comments.Name, Reporting.ReptTo
FROM Comments INNER JOIN Reporting ON Comments.Name = Reporting.Name
WHERE (((Reporting.ReptTo)="James&quot)
ORDER BY Comments.OrdNum;

and so on. I only played around with it a little while, but hopefully this will get you started.

BTW, this is in Access 2000, I don't have Access 97.

Let us know if this helps.