If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help: Multi-table design
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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200709/1 |
#2
|
|||
|
|||
Help: Multi-table design
On Sep 26, 8:45 pm, "benyod79 via AccessMonster.com" u23098@uwe
wrote: 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. -- |
#3
|
|||
|
|||
Help: Multi-table design
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. 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200709/1 |
#4
|
|||
|
|||
Help: Multi-table design
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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200709/1 |
#5
|
|||
|
|||
Help: Multi-table design
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 "benyod79 via AccessMonster.com" wrote: 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. 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200709/1 |
#6
|
|||
|
|||
Help: Multi-table design
On Sep 27, 7:16 pm, mscertified wrote:
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. -- |
#7
|
|||
|
|||
Help: Multi-table design
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. Jamie Collins wrote: 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. -- -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200709/1 |
Thread Tools | |
Display Modes | |
|
|