In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.
COURSE of the MONTH
13 days, 20 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Regarding database design
Posted on 2014-03-29
Hi
I am writing the code in struts2 with spring security by assigning the roles and permissions. I am trying to accomplish the following:
There is a status of the employee as bench or blocked. Blocked means the employee is alloted for a project but not yet assigned. An employee (by default in bench state) can be blocked by Project manager. Blocking indicates that employee is alloted for a particular project and if not assigned into the project in two weeks he will be released, indicates that the employee is in bench again.
An blocked employee can be made assigned if he selected into the project. Assigned is the subset of block.
Clicking a link called assign, the status of the employee (default is bench) should change from bench to assigned. (assigned is a status given to an employee).
Clicking a link called block, the status of the employee (default is bench) should change from bench to blocked. (blocked is also status of the employee).
Clicking a link called release, the status of the employee should change from assigned to release.
A resource can be blocked which means they can be assigned a project but not yet be assigned. A blocked resource that has not assigned for two weeks needs to be released automatically, how can I do that?
How should I structure the database for this? Also, how do I declare the pojo classes for this type of design?
Blocked means he is into project but not yet confirmed, assigned means he is into the project and confirmed as billing.
How to design the pojo classes and database design for this type of situation ?
I just want the table structure and pojo classes so that i can develop the coding
thanks in advance
I am writing the code in struts2 with spring security by assigning the roles and permissions. I am trying to accomplish the following:
There is a status of the employee as bench or blocked. Blocked means the employee is alloted for a project but not yet assigned. An employee (by default in bench state) can be blocked by Project manager. Blocking indicates that employee is alloted for a particular project and if not assigned into the project in two weeks he will be released, indicates that the employee is in bench again.
An blocked employee can be made assigned if he selected into the project. Assigned is the subset of block.
Clicking a link called assign, the status of the employee (default is bench) should change from bench to assigned. (assigned is a status given to an employee).
Clicking a link called block, the status of the employee (default is bench) should change from bench to blocked. (blocked is also status of the employee).
Clicking a link called release, the status of the employee should change from assigned to release.
A resource can be blocked which means they can be assigned a project but not yet be assigned. A blocked resource that has not assigned for two weeks needs to be released automatically, how can I do that?
How should I structure the database for this? Also, how do I declare the pojo classes for this type of design?
Blocked means he is into project but not yet confirmed, assigned means he is into the project and confirmed as billing.
How to design the pojo classes and database design for this type of situation ?
I just want the table structure and pojo classes so that i can develop the coding
thanks in advance
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
-
5
12 Comments
Active today
I think you are mixing the design/architecture of the database and the business process within the application.
An employee has four states of being: available/benched, blocked, assigned, released
However, in reality available, benched, released means the employees can be selected into an existing or a new project, correct.
The two week period means you have to have a blocked table through which you have a scheduled job that will review whether there is a record whose expiry time is older than now() in this case, the emoloyeedId is used to change the employee status from blocked, to benched/available.
You then would have an assigned table where you can have, the project, the employee, and the assigned date.
If a possibility exists that an employee can be in the project for a specific set of tasks and upon their completion, can be released, would need a separate transactional assigned table, that will have similar table as the relationship one from before, but would include a unassigned/complete date that will maintain the records for employee assignment to a project and when the project concluded or the employee was released.
Unfamiliar with the strut2 and pojo to address.
An employee has four states of being: available/benched, blocked, assigned, released
However, in reality available, benched, released means the employees can be selected into an existing or a new project, correct.
The two week period means you have to have a blocked table through which you have a scheduled job that will review whether there is a record whose expiry time is older than now() in this case, the emoloyeedId is used to change the employee status from blocked, to benched/available.
You then would have an assigned table where you can have, the project, the employee, and the assigned date.
If a possibility exists that an employee can be in the project for a specific set of tasks and upon their completion, can be released, would need a separate transactional assigned table, that will have similar table as the relationship one from before, but would include a unassigned/complete date that will maintain the records for employee assignment to a project and when the project concluded or the employee was released.
Unfamiliar with the strut2 and pojo to address.
Hi Arnold, the solution given by you is 50% understandable to me. I am not understanding the table struture for it . But your analyzation is right. Bench and release are going to the same state as bench.
Active today
Wouldn't available be the same as well?
employee table.
blocked relationship table between a projects table and the employee table. with the expiration date
i.e.
ProjectID, employeeID,Expiration date (entries here are deleted when expired or when the user is assigned/released)
1, 3,2014-04-10
Then you have an active transactional and relationship table depending on whether there is a possibility that a user might be assigned to the project for a portion of a time versus for the duration of the project. The distinction deals with accounting for individuals that worked on the project for a period shorter than the duration of the project.
active relationship table (when the user is deactivated or the project is done, entries here are deleted make sure to delete these entries after updating the transactional table.)
projectID,employeeID, Date Activated
Active transactional table
projectID, employeeID, date activated,date released(project completed)
You could use view to join employee and the blocked/assigned tables with conditional check to identify whether the user is available/released/benched
The use of a view deals with whether performing the query on the fly is frequent enough and uses up more resources than the auto-updating view.
employee table.
blocked relationship table between a projects table and the employee table. with the expiration date
i.e.
ProjectID, employeeID,Expiration date (entries here are deleted when expired or when the user is assigned/released)
1, 3,2014-04-10
Then you have an active transactional and relationship table depending on whether there is a possibility that a user might be assigned to the project for a portion of a time versus for the duration of the project. The distinction deals with accounting for individuals that worked on the project for a period shorter than the duration of the project.
active relationship table (when the user is deactivated or the project is done, entries here are deleted make sure to delete these entries after updating the transactional table.)
projectID,employeeID, Date Activated
Active transactional table
projectID, employeeID, date activated,date released(project completed)
You could use view to join employee and the blocked/assigned tables with conditional check to identify whether the user is available/released/benched
The use of a view deals with whether performing the query on the fly is frequent enough and uses up more resources than the auto-updating view.
Hi Arnold, thanks for the solution. What i have understood from your solution is that employee table, blocked table, projects table are there. can you please give me the table strutcuture clearly with the fields and relationships ?
A project manager can also block/assign an employee. Blocked means he will be there blocked for two weeks. Assigned means he is into project and the duration is not specified here.
thanks
swapna
A project manager can also block/assign an employee. Blocked means he will be there blocked for two weeks. Assigned means he is into project and the duration is not specified here.
thanks
swapna
Hi Arnold, the solution was really very nice and clearly explained. I was just looking the ORM kind of relation w.r.t pojo classes and tables. I am using hibernate for this application. I need to get the pojo classes for this and relation to the corresponding table.
The solution given by arnold is very good but i am dilema in writing java pojo classes and setting the relation between them.
thanks & regards,
swapna
The solution given by arnold is very good but i am dilema in writing java pojo classes and setting the relation between them.
thanks & regards,
swapna
Active today
Do you have an example of a pojo class and a table relationship that you already have working. I can look at those and see if I can reverse engineer it such that I might be able to provide guidance for the pojo class with the database design discussed here.
Another option, if you do not already have the mysql workbench, you should get it from http://dev.mysql.com/downloads/tools/
I think there is also toad for mysql http://www.quest.com/toad-for-mysql/
The workbench can be used to create the database design.
Another option, if you do not already have the mysql workbench, you should get it from http://dev.mysql.com/downloads/tools/
I think there is also toad for mysql http://www.quest.com/toad-for-mysql/
The workbench can be used to create the database design.
Hi Arnold, i am working on the pojo classes.
In this one i have developed one pojo class for employee. In that a property called projectStatus is kept. ProjectStatus is another pojo class with three pojo classes as instance viriables in it as bench, block, assign.
class Empoloyee{
private String empId;
private ProjectStatus projectStatus;
private Project projectId;
}
class ProjectStatus{
private Bench bench;
private Block block;
private Assign assign;
}
When Resource manager creates a employee profile the default state of the employee is bench. How to acheive this using java (servlets or struts2) ?
What should be the relation between employee and project status.
Any bench employee can be blocked by an Resource manager, blocking indicates the state changes from bench to block and block state is for two weeks. What ever the pojo classes i have created are right or wrong ? please guide me
thanks & regards
swapna
In this one i have developed one pojo class for employee. In that a property called projectStatus is kept. ProjectStatus is another pojo class with three pojo classes as instance viriables in it as bench, block, assign.
class Empoloyee{
private String empId;
private ProjectStatus projectStatus;
private Project projectId;
}
class ProjectStatus{
private Bench bench;
private Block block;
private Assign assign;
}
When Resource manager creates a employee profile the default state of the employee is bench. How to acheive this using java (servlets or struts2) ?
What should be the relation between employee and project status.
Any bench employee can be blocked by an Resource manager, blocking indicates the state changes from bench to block and block state is for two weeks. What ever the pojo classes i have created are right or wrong ? please guide me
thanks & regards
swapna
Active today
IMHO, you should first define the data structure and the business process.
Once you have that, defining pojo classes might become simpler, you seem to be caught between defining the pojo classes and the database structure. When both are changeable, it complicates things. When you have the data/business process defined you could adjust the data structure if needed during the definition of the pojo classes one at a time.
Once you have that, defining pojo classes might become simpler, you seem to be caught between defining the pojo classes and the database structure. When both are changeable, it complicates things. When you have the data/business process defined you could adjust the data structure if needed during the definition of the pojo classes one at a time.
Featured Post
Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption. Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.
This video will show you how to get GIT to work in Eclipse.
It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
Suggested Courses
Course of the Month13 days, 20 hours left to enroll
801 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.