Here's one I have some preliminary ideas about, but I need help designing
the tables, and I really don't know where to start on the query.
I have a pre-existing table of people that is already used for many things.
For the new feature, in some fashion, people in that table have to be
defined as "Consumers" or "Workers". People in that table may also need to
be defined as other things in the future. I don't want to alter the table
if I can help it, and I suspect that's not the right approach anyway.
Consumers and Workers need to meet requirements before they can participate
in a program.
Consumers and Workers can be assigned to each other in one-to-one
relationships before they meet these requirements.
The relationship is "pending" if either the Consumer, the Worker, or both
does not meet one or more of the requirements.
PROBLEM: I need a query that shows the relationships that are "pending".
I've assumed that I'm going to need a lookup table of pre-defined
requirements in order to have any hope of producing this query.
&& This table is a required element. I can't create a table of just
"Consumers" or just "Workers".
CREATE CURSOR people (PK I, name C(10))
INSERT INTO people (PK, name) VALUES (1,"Joe")
INSERT INTO people (PK, name) VALUES (2,"Bill")
INSERT INTO people (PK, name) VALUES (3,"Pete")
INSERT INTO people (PK, name) VALUES (4,"Ed")
INSERT INTO people (PK, name) VALUES (5,"Mary")
INSERT INTO people (PK, name) VALUES (6,"Ann")
INSERT INTO people (PK, name) VALUES (7,"Sally")
&& Approach 1: Two separate tables for defining "people" as "Consumers" or
"Workers"
CREATE CURSOR consumers (PK I, peoFK I)
INSERT INTO consumers (PK, peoFK) VALUES (1,1)
INSERT INTO consumers (PK, peoFK) VALUES (2,2)
INSERT INTO consumers (PK, peoFK) VALUES (3,3)
INSERT INTO consumers (PK, peoFK) VALUES (4,4)
CREATE CURSOR workers (PK I, workFK I)
INSERT INTO workers (PK, peoFK) VALUES (1,5)
INSERT INTO workers (PK, peoFK) VALUES (2,6)
INSERT INTO workers (PK, peoFK) VALUES (3,7)
&& Approach 2: A single table that can define "people" as "Consumers" or
"Workers" (or other things, presumably,
&& and the same person can be defined more than once).
CREATE CURSOR peotypes (PK I, peoFK I, type C(1))
INSERT INTO peotypes (PK, peoFK, type) VALUES (1,1,"C")
INSERT INTO peotypes (PK, peoFK, type) VALUES (2,2,"C")
INSERT INTO peotypes (PK, peoFK, type) VALUES (3,3,"C")
INSERT INTO peotypes (PK, peoFK, type) VALUES (4,4,"C")
INSERT INTO peotypes (PK, peoFK, type) VALUES (5,5,"W")
INSERT INTO peotypes (PK, peoFK, type) VALUES (6,6,"W")
INSERT INTO peotypes (PK, peoFK, type) VALUES (7,7,"W")
&& Table that stores the relationships between Consumers and Workers
CREATE CURSOR conwork (PK I, conFK I, workFK I, program C(10))
conwork: PK, conFK, workFK, program
INSERT INTO conwork (PK, conFK, workFK, program) VALUES (1,1,3,"Prog1")
INSERT INTO conwork (PK, conFK, workFK, program) VALUES (2,2,2,"Prog2")
INSERT INTO conwork (PK, conFK, workFK, program) VALUES (3,3,1,"Prog3")
INSERT INTO conwork (PK, conFK, workFK, program) VALUES (4,2,3,"Prog1")
INSERT INTO conwork (PK, conFK, workFK, program) VALUES (5,4,2,"Prog2")
&& Lookup table of requirements
CREATE CURSOR reqs (PK I, type C(1), name C(15)) && Type indicates whether
required for Consumer or Worker
INSERT INTO reqs (PK, type, name) VALUES (1,"C","signature")
INSERT INTO reqs (PK, type, name) VALUES (2,"C","approval")
INSERT INTO reqs (PK, type, name) VALUES (3,"W","TB test")
INSERT INTO reqs (PK, type, name) VALUES (4,"W","insurance")
&& Table of requirements met by Consumers
CREATE CURSOR conreqs (PK I, conFK I, reqFK I, donedate D)
INSERT INTO conreqs (PK, conFK, reqFK, donedate) VALUES (1,1,1,{^2010/1/1})
INSERT INTO conreqs (PK, conFK, reqFK, donedate) VALUES (2,1,2,{^2010/1/1})
INSERT INTO conreqs (PK, conFK, reqFK, donedate) VALUES (3,2,2,{^2010/2/1})
INSERT INTO conreqs (PK, conFK, reqFK, donedate) VALUES (4,4,1,{^2010/1/1})
INSERT INTO conreqs (PK, conFK, reqFK, donedate) VALUES (5,4,2,{^2010/1/1})
&& Table of requirements met by Workers
CREATE CURSOR workreqs (PK I, workFK I, reqFK I, donedate D)
INSERT INTO workreqs (PK, workFK, reqFK, donedate) VALUES (1,3,3,{^2010/1/1})
INSERT INTO workreqs (PK, workFK, reqFK, donedate) VALUES (2,3,4,{^2010/1/1})
INSERT INTO workreqs (PK, workFK, reqFK, donedate) VALUES (3,2,3,{^2010/2/1})
Results should look like this:
Consumer Program Worker
Bill Prog2 Ann && Bill is missing
signature; Ann missing insurance
Bill Prog1 Sally && Bill is missing
signature
Pete Prog3 Mary && Pete lacks signature
and approval; Mary has no TB test or insurance
Ed Prog2 Ann && Ann is missing insurance
Thanks very much for any suggestions.
Ken Dibble
www.stic-cil.org
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
