Privileges, Security & Database Design Issues

[Smriti Dev]

Hi There,

I'm creating a MS ACCESS database using Ms Access 2000 to store
Interpretation requests by different departments in a hospital and
Interpreter availability. All internal departments will be calling in or
faxing their Interpretation requests to Intake staff at the Interpretation
department and the intake staff will feed the request into the database.
However, their is one department, Rehab, that is going to be going to be
able to put in Interpretation requests thru an access form and they want
the capacity to view the appointments (Interpretation Requests) they have
requested.

I have the following tables in my database:

1.. Interpreters
2.. Interpreter Type
3.. InterpreterAvai lability Schedule
4.. Internal Departments
5.. Rehab Solutions
6.. Intake Staff
7.. Rehab Staff
8.. Interpreter requestsbyInter nalDepts
9.. Interpreter requestsbyRehab Solutions
10.. AppointmentBook ingStatus
11.. Appointments
12.. AppointmentType
13.. Invoice
14.. Language
I read on 'How I use Microsoft Access User-Level Security' from
www.geocities.com/jacksonmacd dated
march 2, 2004 that
"Permission s are applied at the table level. You can't configure Jet to
allow some users to read or write to the whole table while restricting other
users to select rows or columns."

I am wondering what is the best way to design the tables for Interpretation
Request. Is it possible to keep one table for (ie. InterpreterRequ ests vs .
the tables:
1.. Interpreter requestsbyInter nalDepts
2.. Interpreter requestsbyRehab Solutions I do want Rehab staff to view
the appointments they have requested
and still allow Rehab to view just the appointments they have requested?
Thanks so much for your
help with this.

Thanks kindly,
Smriti

[MGFoster]

Smriti Dev wrote:

Hi There,

I'm creating a MS ACCESS database using Ms Access 2000 to store
Interpretation requests by different departments in a hospital and
Interpreter availability. All internal departments will be calling in or
faxing their Interpretation requests to Intake staff at the Interpretation
department and the intake staff will feed the request into the database.
However, their is one department, Rehab, that is going to be going to be
able to put in Interpretation requests thru an access form and they want
the capacity to view the appointments (Interpretation Requests) they have
requested.

I have the following tables in my database:

1.. Interpreters
2.. Interpreter Type
3.. InterpreterAvai lability Schedule
4.. Internal Departments
5.. Rehab Solutions
6.. Intake Staff
7.. Rehab Staff
8.. Interpreter requestsbyInter nalDepts
9.. Interpreter requestsbyRehab Solutions
10.. AppointmentBook ingStatus
11.. Appointments
12.. AppointmentType
13.. Invoice
14.. Language
I read on 'How I use Microsoft Access User-Level Security' from
www.geocities.com/jacksonmacd dated
march 2, 2004 that
"Permission s are applied at the table level. You can't configure Jet to
allow some users to read or write to the whole table while restricting other
users to select rows or columns."

I am wondering what is the best way to design the tables for Interpretation
Request. Is it possible to keep one table for (ie. InterpreterRequ ests vs .
the tables:
1.. Interpreter requestsbyInter nalDepts
2.. Interpreter requestsbyRehab Solutions I do want Rehab staff to view
the appointments they have requested
and still allow Rehab to view just the appointments they have requested?
Thanks so much for your
help with this.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Put all the Interpreter requests into one table, w/ a column that
indicates which departments have made the requests.

In Access security you can assign users to Groups (aka Roles in other
RDBMS). Create a group for each Department that will be using the DB
and assign users to the appropriate group(s). Create a table of
groups/departments like this:

Table: DepartmentGroup s
Columns: GroupName - the name of the security group
Department - the name of the hospital department

Load it with the appropriate info. E.g.:

GroupName Department
========= ==========
REHAB Rehabilitation
ACCT Accounting
OBGYN Obstetrics/Gyneocology
.... etc. ...

Create a View (Select Query) that will read the Interpreter Requests
table & filter the records according to which user is using the view.
The view will have a VBA function that will determine which group the
CurrentUser is assigned to & then display only those records that have
info for that group's department. E.g.:

SELECT IR.interpret_da te_time, IR.room_no, IR.language
FROM InterpreterRequ ests As IR INNER JOIN DepartmentGroup s As DG
ON IR.department = DG.Department
WHERE DG.GroupName = GetUserGroup()

The function GetUserGroup() would determine the group the current user
belongs to & return it to the query/view. The query/view will show only
the records for the department that group is associated with.

There's a book _Access # Developer's Handbook_ by K.Getz, et. al. (the #
indicates which version of Access), which describes a routine that
determines the user's group. It is also on the WEB somewhere try a
search in Google, or your favorite search engine.
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQt6iEYechKq OuFEgEQIrPQCgya k9hjR8s8kooWDVx s9zzZxYjoYAoOYl
UuuJFKULfyDkfAQ lr0DQYjkX
=1ZnU
-----END PGP SIGNATURE-----

[Smriti Dev]

Hi MGFoster,

Thanks so much for your response. It was very helpful. I will look for the
book you suggested at the bookstore.

I do have another question, I have interpreters who have varying schedules
and once their availability is put into the database, I need to pull out all
the Interpreters of a certain language type
that are available and see if they can potentially fulfill Interpretation
Requests for the time, date and language the request was made by the
department.

I am confused about how to set up the relationships for this in the E-R
diagram and also not sure what type of query to use for this.

Thanks so much for your help.

Kind Regards,
Smriti

"MGFoster" <[e-mail] > wrote in message
news:sm******** ********@newsre ad1.news.pas.ea rthlink.net...

Smriti Dev wrote:

Hi There,

I'm creating a MS ACCESS database using Ms Access 2000 to store
Interpretation requests by different departments in a hospital and
Interpreter availability. All internal departments will be calling in or faxing their Interpretation requests to Intake staff at the Interpretation department and the intake staff will feed the request into the database.
However, their is one department, Rehab, that is going to be going to be
able to put in Interpretation requests thru an access form and they want the capacity to view the appointments (Interpretation Requests) they have requested.

I have the following tables in my database:

1.. Interpreters
2.. Interpreter Type
3.. InterpreterAvai lability Schedule
4.. Internal Departments
5.. Rehab Solutions
6.. Intake Staff
7.. Rehab Staff
8.. Interpreter requestsbyInter nalDepts
9.. Interpreter requestsbyRehab Solutions
10.. AppointmentBook ingStatus
11.. Appointments
12.. AppointmentType
13.. Invoice
14.. Language
I read on 'How I use Microsoft Access User-Level Security' from
www.geocities.com/jacksonmacd dated
march 2, 2004 that
"Permission s are applied at the table level. You can't configure Jet to
allow some users to read or write to the whole table while restricting other users to select rows or columns."

I am wondering what is the best way to design the tables for Interpretation Request. Is it possible to keep one table for (ie. InterpreterRequ ests vs . the tables:
1.. Interpreter requestsbyInter nalDepts
2.. Interpreter requestsbyRehab Solutions I do want Rehab staff to view the appointments they have requested
and still allow Rehab to view just the appointments they have requested?
Thanks so much for your
help with this.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Put all the Interpreter requests into one table, w/ a column that
indicates which departments have made the requests.

In Access security you can assign users to Groups (aka Roles in other
RDBMS). Create a group for each Department that will be using the DB
and assign users to the appropriate group(s). Create a table of
groups/departments like this:

Table: DepartmentGroup s
Columns: GroupName - the name of the security group
Department - the name of the hospital department

Load it with the appropriate info. E.g.:

GroupName Department
========= ==========
REHAB Rehabilitation
ACCT Accounting
OBGYN Obstetrics/Gyneocology
... etc. ...

Create a View (Select Query) that will read the Interpreter Requests
table & filter the records according to which user is using the view.
The view will have a VBA function that will determine which group the
CurrentUser is assigned to & then display only those records that have
info for that group's department. E.g.:

SELECT IR.interpret_da te_time, IR.room_no, IR.language
FROM InterpreterRequ ests As IR INNER JOIN DepartmentGroup s As DG
ON IR.department = DG.Department
WHERE DG.GroupName = GetUserGroup()

The function GetUserGroup() would determine the group the current user
belongs to & return it to the query/view. The query/view will show only
the records for the department that group is associated with.

There's a book _Access # Developer's Handbook_ by K.Getz, et. al. (the #
indicates which version of Access), which describes a routine that
determines the user's group. It is also on the WEB somewhere try a
search in Google, or your favorite search engine.
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQt6iEYechKq OuFEgEQIrPQCgya k9hjR8s8kooWDVx s9zzZxYjoYAoOYl
UuuJFKULfyDkfAQ lr0DQYjkX
=1ZnU
-----END PGP SIGNATURE-----

[MGFoster]

Smriti Dev wrote:

Hi MGFoster,

Thanks so much for your response. It was very helpful. I will look for the
book you suggested at the bookstore.

I do have another question, I have interpreters who have varying schedules
and once their availability is put into the database, I need to pull out all
the Interpreters of a certain language type
that are available and see if they can potentially fulfill Interpretation
Requests for the time, date and language the request was made by the
department.

I am confused about how to set up the relationships for this in the E-R
diagram and also not sure what type of query to use for this.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I don't know how to create that query since your table list did not
include an InterpreterAvai lability table, nor, its columns. A DDL (Data
Declaration Language description of the table(s)) would be nice; and,
some sample data. I'd guess the InterpreterAvai lability table would
hold the start & end date/times of availability of each interpreter -
IOW, a work schedule table.

Re: your listed tables. You have 2 tables Intake Staff & Rehab Staff
that probably would be better as one table w/ a column indicating the
department of the staff member (intake or rehab - or some other
department).

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQt69qYechKq OuFEgEQKVIwCgrI uiglAbLwXTwqtvz giyWM9eSYMAnjOP
WqNz/bBbaiOvTa7rX71v vlJ7
=3eVS
-----END PGP SIGNATURE-----

[Smriti Dev]

Hi MGFoster,

Thanks for your reply and sorry for the delay in getting back to you. I was
working on the DDL
and also wanted to give you some background information.

Please see below for background info and DDL info.

Background Information:
I want to create a database to store information about Interpreters and
track the appointments they have. Interpreters are requested on a
continuous basis by Internal Departments at the UHN, and also by Rehab
Solutions. Internal departments are not charged for Interpreters but Rehab
Solutions is charged. Rehab Solutions should be able to view the request
they make and edit it. Sometimes Interpretation Requests are made for a
group of people. The number of people in the group are to be recorded if it
is a request for a group.



Information about Interpreter's will include their names, contact
information and how much they are paid/hr, the type of Interpreter and the
languages they speak the timings they are available to work. An Interpreter
can only have one appointment scheduled at a certain time.

Departments make Interpreter requests by indicating the time they require an
interpreter and the language they require. Additional information about the
appointment is stored including the Patient's Lastname, MRN number, location
of the appointment, the care provider who will be at the appointment and the
type of care provider they are & any comments. Each department can make many
requests for Interpreters. Departments are invoiced for the Interpretations
they request.

Information about Intake staff who will be booking the Interpreters will be
stored including their Staff Numbers, names, email addresses.

The status that the appointment booking is at will also be recorded. Options
include pending, assigned, cancelled, review.

DDL:

Interpreters Interpreter_ Role
Interpreter_Ava ilability Schedule
Care Provider
CareProvider_Ro le

InterpreterID (PK)
InterpreterRole ID (PK)
InterpreterAvai labilityID(PK)
CareproviderID (PK)
CareProviderRol e_ID(PK)

Interpreter Number
InterpreterRole

(i.e. staff, volunteer, contract)
InterpreterID (FK)

-so we know which Interpreter is avail.
CareProviderFir stName
Care_Provider_T ype

(ie. nurse)

LastName
InterpreterType Cost
DataInterpreter Avail
CareProviderLas tName
FirstName
InterpreterType Percentage
StartTimeInterp reterAvail
CareProviderPho ne
PhoneNum

EndTimeInterpre terAvail
CareProviderPho neExtension
Email
CareProviderRol e_ID(fk)
InterpreterAvai labilityID (FK)

InterpreterType ID(FK)

LanguageID (FK)







Staff Staff_Dept Interpreter requests
Area

(dept, rehab)
EnterpriseType
StaffID (PK)
Staff_dept(PK)
InterpreterReqI D (PK)
AreaID (PK)
EnterpriseID(PK )
StaffNum
Staffing_dept_d escription
CareProviderID( fk)
RequestFrom
EnterpriseName
LastName

LanguageID(fk)


FirstName

AreaID(fk)


Email

PatLastName


HPhoneNum

PatFirstName


WPhoneNum

MRN


Staff_deptID(fk )

EnterpriseID(fk )


SiteID(fk)


location_buildi ng


location_floor


AppointmentType ID(fk)


DepartmentID(fk )


Comments


Status_id (fk)


AttachementID


GroupID









Site
Programme

SiteID (PK)
GroupID (PK)

Site Name (ie. TGH, Corporate, PMH, TWH)
GroupName
Number_of_Stude nts
Group (y/n)











Department
AppointmentType
Invoice
Language
Appointment

Dept_id (PK)
AppointmentType ID(PK)
InvoiceID
LanguageID (PK)
ApointmentID

DepartmentName
Appointment Type (ie. Admission, Assessment, Discharge, Family Meeting,
Program)
InvoiceNo
LanguageDescrip tion
InterpreterReqI D (Fk)

Date

Interpreter(ID) fk

JobID

Appointment StartTime

Terms

AppointmentEndT ime

RequestConfirmD ate

Called

AppointementID( Fk)

LanguageID(FK)

ScheduleDate



Appointment Status
Attachments
Job
Group
DepartmentGroup s

StatusID(PK)
AttachmentID(PK )
JobID(PK)
GroupID (PK)
departmetngroup ID (PK)

Status_descript ion (eg. Cancelled)
LocationofAttac hemnt
Project
AreaID(fk)
GroupName

Qty
user


Description

Rate



Thanks for your help with this. I look forward to hearing from you.

Thanks Kindly,
Smriti

"MGFoster" <[e-mail] > wrote in message
news:O4******** *********@newsr ead1.news.pas.e arthlink.net...

Smriti Dev wrote:

Hi MGFoster,

Thanks so much for your response. It was very helpful. I will look for the book you suggested at the bookstore.

I do have another question, I have interpreters who have varying schedules and once their availability is put into the database, I need to pull out all the Interpreters of a certain language type
that are available and see if they can potentially fulfill Interpretation Requests for the time, date and language the request was made by the
department.

I am confused about how to set up the relationships for this in the E-R
diagram and also not sure what type of query to use for this.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I don't know how to create that query since your table list did not
include an InterpreterAvai lability table, nor, its columns. A DDL (Data
Declaration Language description of the table(s)) would be nice; and,
some sample data. I'd guess the InterpreterAvai lability table would
hold the start & end date/times of availability of each interpreter -
IOW, a work schedule table.

Re: your listed tables. You have 2 tables Intake Staff & Rehab Staff
that probably would be better as one table w/ a column indicating the
department of the staff member (intake or rehab - or some other
department).

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQt69qYechKq OuFEgEQKVIwCgrI uiglAbLwXTwqtvz giyWM9eSYMAnjOP
WqNz/bBbaiOvTa7rX71v vlJ7
=3eVS
-----END PGP SIGNATURE-----