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_Availability Schedule
Care Provider
CareProvider_Role
InterpreterID (PK)
InterpreterRoleID (PK)
InterpreterAvailabilityID(PK)
CareproviderID (PK)
CareProviderRole_ID(PK)
Interpreter Number
InterpreterRole
(i.e. staff, volunteer, contract)
InterpreterID (FK)
-so we know which Interpreter is avail.
CareProviderFirstName
Care_Provider_Type
(ie. nurse)
LastName
InterpreterTypeCost
DataInterpreterAvail
CareProviderLastName
FirstName
InterpreterTypePercentage
StartTimeInterpreterAvail
CareProviderPhone
PhoneNum
EndTimeInterpreterAvail
CareProviderPhoneExtension
Email
CareProviderRole_ID(fk)
InterpreterAvailabilityID (FK)
InterpreterTypeID(FK)
LanguageID (FK)
Staff Staff_Dept Interpreter requests
Area
(dept, rehab)
EnterpriseType
StaffID (PK)
Staff_dept(PK)
InterpreterReqID (PK)
AreaID (PK)
EnterpriseID(PK)
StaffNum
Staffing_dept_description
CareProviderID(fk)
RequestFrom
EnterpriseName
LastName
LanguageID(fk)
FirstName
AreaID(fk)
Email
PatLastName
HPhoneNum
PatFirstName
WPhoneNum
MRN
Staff_deptID(fk)
EnterpriseID(fk)
SiteID(fk)
location_building
location_floor
AppointmentTypeID(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_Students
Group (y/n)
Department
AppointmentType
Invoice
Language
Appointment
Dept_id (PK)
AppointmentTypeID(PK)
InvoiceID
LanguageID (PK)
ApointmentID
DepartmentName
Appointment Type (ie. Admission, Assessment, Discharge, Family Meeting,
Program)
InvoiceNo
LanguageDescription
InterpreterReqID (Fk)
Date
Interpreter(ID) fk
JobID
Appointment StartTime
Terms
AppointmentEndTime
RequestConfirmDate
Called
AppointementID(Fk)
LanguageID(FK)
ScheduleDate
Appointment Status
Attachments
Job
Group
DepartmentGroups
StatusID(PK)
AttachmentID(PK)
JobID(PK)
GroupID (PK)
departmetngroupID (PK)
Status_description (eg. Cancelled)
LocationofAttachemnt
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" <me@privacy.com> wrote in message
news:O4*****************@newsread1.news.pas.earthl ink.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 InterpreterAvailability 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 InterpreterAvailability 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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQt69qYechKqOuFEgEQKVIwCgrIuiglAbLwXTwqtvzgiyWM 9eSYMAnjOP
WqNz/bBbaiOvTa7rX71vvlJ7
=3eVS
-----END PGP SIGNATURE-----