Help: Multi-table design

[benyod79 via AccessMonster.com]

I need help designing a few tables as I'm stuck and just can't think outside
the box. Here's the details:

tblEmployees
- EmpID (PK)
- FirstName, etc....details pertaining to each employee

tblJobs
- JobID (PK)
- EmpID (linked to tblEmployees.EmpID)
- FacilityName, Position, etc.....details pertaining to each job for each
employee

There are groups that each employee needs to enroll with for each job. Each
group has criteria that needs to be kept track of, but all groups don't have
the same process.

i.e.
Medicare
- Date Application was sent
- Date Signature was received

Medicaid
- Date Application was sent
- Date Signature was received
- Date Medicaid number is received

Here's my thought to solve this:

tblEnrollmentLink
- JobID (linked to tblJobs.JobID)
- GroupID (linked to tblEnrollmentGroups)

tblEnrollmentGroups
- GroupID (PK)
- GroupName, Address, etc

tblEnrollmentGroupProcesses
- ProcessID (PK)
- GroupID (linked to tblEnrollmentGroups)
- ProcessDescription

How do I make a table that links the processes needed for each group to the
group? Basically for each ProcessDescription, I'd like to be able to enter
data pertaining to that process.

If you're confused, welcome to the club. If not, can you please help?

Thanks

 

[Jamie Collins]

I need help designing a few tables as I'm stuck and just can't think outside
the box. Here's the details:

tblEmployees
- EmpID (PK)
- FirstName, etc....details pertaining to each employee

tblJobs
- JobID (PK)
- EmpID (linked to tblEmployees.EmpID)
- FacilityName, Position, etc.....details pertaining to each job for each
employee

There are groups that each employee needs to enroll with for each job. Each
group has criteria that needs to be kept track of, but all groups don't have
the same process.

i.e.
Medicare
- Date Application was sent
- Date Signature was received

Medicaid
- Date Application was sent
- Date Signature was received
- Date Medicaid number is received

Here's my thought to solve this:

tblEnrollmentLink
- JobID (linked to tblJobs.JobID)
- GroupID (linked to tblEnrollmentGroups)

tblEnrollmentGroups
- GroupID (PK)
- GroupName, Address, etc

tblEnrollmentGroupProcesses
- ProcessID (PK)
- GroupID (linked to tblEnrollmentGroups)
- ProcessDescription

How do I make a table that links the processes needed for each group to the
group? Basically for each ProcessDescription, I'd like to be able to enter
data pertaining to that process.

If you're confused, welcome to the club. If not, can you please help?

I have similar dilemmas and it basically comes down to this: I have a
number of entity types which have similar attributes (a cat has legs,
an armadillo has legs, a darts match has legs) but is there any
advantage to adding a layer of abstraction? if so, where ('Animals'
could be more useful than 'ThingsWithLegs'). Probably the best
starting point is to assume no abstraction and see just how hard to
write the integrity constraints prove or how tedious the repetition
becomes ;-)

It could be a cultural difference but I'm having a hard time relating
the entity type 'job-participating employee' and the attribute
'medicare application sent date': does this date relate to the person,
employee, job or group? Perhaps you could post some sample data? TIA.

Jamie.

--

 

[benyod79 via AccessMonster.com]

It could be a cultural difference but I'm having a hard time relating

the entity type 'job-participating employee' and the attribute
'medicare application sent date': does this date relate to the person,
employee, job or group? Perhaps you could post some sample data? TIA.

Each employee is unique. The job that each employee has is unique to that
employee. Each employee at each job needs to enroll with different groups
(Medicare, Medicaid, etc). However, the data needed for each group is not
consistent (Medicare does not need the same info as Medicaid). Therefore, I'd
like to be able to breakdown what's needed for each group. Then when an
employee needs to enroll with a particular group for a specific job, that
data is captured and stored correctly.

The reason I want to set it up with flexibility is that we change enrollment
groups relatively frequently. I would like to easily add a new group, then
list the requirements for that group. Then when I select an employee and
his/her job, I'd select a group to enroll with and bam....those items that
are needed for that enrollment group are there and I can enter the pertinent
data.

There is not a 1-1 relationship for enrollments and jobs. Each job can and
will have multiple enrollment groups. I'll give some examples to try and
clear this up.

Medicare
Name Job Date App Date Sig
Date Rec'd Confir Rec'd
Palmer Physician @ hospital x 6/5/07 6/6/07 6/9/07
-----
Smith Physician @ hospital x 6/1/07 ----- ------
-----
Jones Physician Assistant @ x 5/25/07 6/2/07 7/1/07
7/3/07

Medicaid
Name Job Date App Date Sig
Linked Number Asgn'd
Palmer Physician @ hospital x 3/31/07 4/3/07 5/9/07
5/15/07
Smith Physician @ hospital x 6/19/07 6/22/07 ------
-----
Jones Physician Assistant @ x 5/22/07 5/27/07 6/1/07
----

So as you can see, the data needed for each enrollment group changes. An easy
solution to this would be to hard code tables for each group (tblMedicare,
tblMedicaid, etc). I don't want to do this because it doesn't allow
flexibility. I'd have to create and link new tables if/when we add different
groups.

 

[benyod79 via AccessMonster.com]

P.S. One more thing. I think I realize this selecting and everything would
all have to take place in a form. Select an employee, select that employee's
job, click on Enrollment button (or whatever) then select the group you want
to enroll with. If it's Medicare, Medicare's requirements pop up with ability
to enter data. If it's Medicaid, Medicaid's requirements pop up with ability
to enter data. Etc, etc, etc.

 

[mscertified]

I developed a similar database. In my database there were different types of
'packages'. Each type of package required tracking a different set of dates.
First I set up a table containg the package types.
Then a table defining all the package date types.
Then a cross reference table relating package types to date types.
Now, when I select a package type, all the dates relating to that package
type pop up in a listbox ready to be updated.
You need to do something similar with your groups and processes.

-Dorian

 

[Jamie Collins]

I developed a similar database. In my database there were different types of
'packages'. Each type of package required tracking a different set of dates.
First I set up a table containg the package types.
Then a table defining all the package date types.
Then a cross reference table relating package types to date types.

You need to do something similar with your groups and processes.

"Need to"? I don't agree.

From the OP's data it seems likely there are transition states e.g.

Signature cannot be received before Application is sent, Medicaid
number cannot be received until Signature is received, etc. If I have
understood correctly, your proposed design would make these
constraints *harder* to define.

I think this is the easy way (aircode):

CREATE TABLE MedicaidJobs
(
JobID INTEGER NOT NULL,
job_type VARCHAR(12) DEFAULT 'Medicaid' NOT NULL,
CHECK (job_type = 'Medicaid'),
FOREIGN KEY (job_type, JobID) REFERENCES ...,
application_sent_date DATETIME,
signature_recieved_date DATETIME,
CONSTRAINT Medicaid__application_before_signature
CHECK (application_sent_date < signature_recieved_date),
medicaid_number_received_date DATETIME,
CONSTRAINT Medicaid__signature_before_number
CHECK (signature_recieved_date < medicaid_number_received_date)
);

BTW those CHECK constraints can be replaced using the Table Validation
Rule, however I prefer multiple rules to provide better granularity in
constraint failure messages (plus CHECK constraints are easier to post
as SQL DDL <g>).

Personally, I avoid nullable columns (subatomic attributes excepted),
so I'd probably use a table of state transitions associated with a
period (using two subatomic attributes, start - and end date) for each
job type for each jobID e.g.

CREATE TABLE JobStateTrasitions
(
job_type VARCHAR(12) NOT NULL REFERENCES ...,
previous_state VARCHAR(30) DEFAULT '{{NONE}}' NOT NULL,
current_state VARCHAR(30) NOT NULL,
UNIQUE (job_type, previous_state, current_state)
)
;
INSERT INTO JobStateTrasitions (job_type, previous_state,
current_state)
VALUES ('Medicaid', '{{NONE}}', 'Processing application')
;
INSERT INTO JobStateTrasitions (job_type, previous_state,
current_state)
VALUES ('Medicaid', 'Processing application', 'Awaiting signature')
;
INSERT INTO JobStateTrasitions (job_type, previous_state,
current_state)
VALUES ('Medicaid', 'Awaiting signature', 'Awaiting Medicaid
Number'
)
;
INSERT INTO JobStateTrasitions (job_type, previous_state,
current_state)
VALUES ('Medicaid', 'Awaiting Medicaid Number', 'Complete')
)
;
CREATE TABLE JobStates
(
JobID INTEGER NOT NULL,
job_type VARCHAR(12) NOT NULL,
FOREIGN KEY (job_type, JobID) REFERENCES ...,
previous_state VARCHAR(30) DEFAULT '{{NONE}}' NOT NULL,
current_state VARCHAR(30) NOT NULL,
FOREIGN KEY (job_type, previous_state, current_state)
REFERENCES JobStateTrasitions (job_type, previous_state,
current_state),
start_date DATETIME NOT NULL,
end_date DATETIME,
CHECK (start_date < end_date),
UNIQUE (JobID, job_type, start_date)
)
;

The latter table requires table-level CHECK constraints to ensure that
for each (JobID, job_type) combination
1) there are no overlapping periods;
2) all periods are contiguous;
3) the earliest period has current_state = '{{NONE}}';
4) the prior period's current_state (where exists) matches
previous_state
(I might even ditch previous_state in this table and instead utilise a
CHECK constraint).

It would take a bit of work but these constraints are doable.

Now, with your proposed design, how would you define constraints to
ensure that for a Medicaid job Signature cannot be received before
Application is sent, etc?

Jamie.

--

 

[benyod79 via AccessMonster.com]

Jamie,

Thanks for the thoughts. In looking at your proposal, I think I see where you
were going with it. Please correct if I've misinterpreted. I also included
below two sample table structures.

Here's how I see it but something just isn't clicking:

MedicaidJobs
JobID (fk - tblJobs.JobID)
Job_Type (fk - JobStates.JobType)
Application_Start_Date
Application_Received_Date

JobStates
JobID (fk - tblJobs.JobID)
Job_Type (fk - JobStateTransitions.Job_Type)
Previous_State
Current_State

JobStateTransitions
Job_Type (pk)
Previous_State
Current_State


If I were to create a table for each of the groups, this is what they'd look
like:

tblMedicaidEnrollments
MedicaidID (pk)(AutoNumber)
JobID (fk - tblJobs.JobID)(Number)
Forms_Sent_To_Provider (Date)
Forms_Received_From_Provider (Date)
App_Sent_to_Medicaid (Date)
Provider_Number_Assigned (Date)
Actual_Number_Assigned (Number)
Linking_Application_Sent (Date)
Provider_Linked (Date)
Locator_Code (Text)

tblMedicareEnrollments
MedicareID (pk) (AutoNumber)
JobID (fk - tblJobs.JobID)(Number)
Application_Completed (Date)
Signature_Sent_To_Provider (Date)
Signature_Received (Date)
Application_Sent (Date)
Forms_Sent (Text)
Confirmation_Sent (Date)
Confirmation_Received (Date)


So based on what you've offered, I've got that JobStateTransitions will hold
the different process state value. However, shouldn't JobStates have a PK?
Then that value would be the fk to MedicaidJobs.JobType.

As I look at this, it starts to be a little bit more clear. But then I get
stuck in a rut. You're solution looks like it's on the right path, but needs
some tweaking. I'm going to look at it more and see if I can get to the
solution.

Let me know if you come up with anything more.

Thanks.

I developed a similar database. In my database there were different types of
'packages'. Each type of package required tracking a different set of dates.

[quoted text clipped - 3 lines]

You need to do something similar with your groups and processes.

"Need to"? I don't agree.

From the OP's data it seems likely there are transition states e.g.

Signature cannot be received before Application is sent, Medicaid
number cannot be received until Signature is received, etc. If I have
understood correctly, your proposed design would make these
constraints *harder* to define.

I think this is the easy way (aircode):

CREATE TABLE MedicaidJobs
(
JobID INTEGER NOT NULL,
job_type VARCHAR(12) DEFAULT 'Medicaid' NOT NULL,
CHECK (job_type = 'Medicaid'),
FOREIGN KEY (job_type, JobID) REFERENCES ...,
application_sent_date DATETIME,
signature_recieved_date DATETIME,
CONSTRAINT Medicaid__application_before_signature
CHECK (application_sent_date < signature_recieved_date),
medicaid_number_received_date DATETIME,
CONSTRAINT Medicaid__signature_before_number
CHECK (signature_recieved_date < medicaid_number_received_date)
);

BTW those CHECK constraints can be replaced using the Table Validation
Rule, however I prefer multiple rules to provide better granularity in
constraint failure messages (plus CHECK constraints are easier to post
as SQL DDL <g>).

Personally, I avoid nullable columns (subatomic attributes excepted),
so I'd probably use a table of state transitions associated with a
period (using two subatomic attributes, start - and end date) for each
job type for each jobID e.g.

CREATE TABLE JobStateTrasitions
(
job_type VARCHAR(12) NOT NULL REFERENCES ...,
previous_state VARCHAR(30) DEFAULT '{{NONE}}' NOT NULL,
current_state VARCHAR(30) NOT NULL,
UNIQUE (job_type, previous_state, current_state)
)
;
INSERT INTO JobStateTrasitions (job_type, previous_state,
current_state)
VALUES ('Medicaid', '{{NONE}}', 'Processing application')
;
INSERT INTO JobStateTrasitions (job_type, previous_state,
current_state)
VALUES ('Medicaid', 'Processing application', 'Awaiting signature')
;
INSERT INTO JobStateTrasitions (job_type, previous_state,
current_state)
VALUES ('Medicaid', 'Awaiting signature', 'Awaiting Medicaid
Number'
)
;
INSERT INTO JobStateTrasitions (job_type, previous_state,
current_state)
VALUES ('Medicaid', 'Awaiting Medicaid Number', 'Complete')
)
;
CREATE TABLE JobStates
(
JobID INTEGER NOT NULL,
job_type VARCHAR(12) NOT NULL,
FOREIGN KEY (job_type, JobID) REFERENCES ...,
previous_state VARCHAR(30) DEFAULT '{{NONE}}' NOT NULL,
current_state VARCHAR(30) NOT NULL,
FOREIGN KEY (job_type, previous_state, current_state)
REFERENCES JobStateTrasitions (job_type, previous_state,
current_state),
start_date DATETIME NOT NULL,
end_date DATETIME,
CHECK (start_date < end_date),
UNIQUE (JobID, job_type, start_date)
)
;

The latter table requires table-level CHECK constraints to ensure that
for each (JobID, job_type) combination
1) there are no overlapping periods;
2) all periods are contiguous;
3) the earliest period has current_state = '{{NONE}}';
4) the prior period's current_state (where exists) matches
previous_state
(I might even ditch previous_state in this table and instead utilise a
CHECK constraint).

It would take a bit of work but these constraints are doable.

Now, with your proposed design, how would you define constraints to
ensure that for a Medicaid job Signature cannot be received before
Application is sent, etc?

Jamie.

--