Complex Database Creation
I am creating a complex database for Employee Scheduler Online. As the name implies I will be collecting information to allow a scheduler to schedule employees for a business.
I apologize for the lengthiness of this, but there is no simple way of explaining this.
There will obviously be more than one business in which I am connecting all areas using a Bus_UUID. The business will only get the information for their business if login Bus_UUID matches what is in the database. The business will never see the Bus_UUID and will only be able to see their created Bus_ID.
First is specific business information in which it is gathered how many weeks a schedule should be created, what day should scheduling begin, which week the scheduling should begin, the time type (standard/military) to be used for the business, minimum time off requirements for general employees/management, and lunch requirements. This part is already taken care of.
Next is shifts referred to as Business Hours. Their will be an option of 4 shifts for the business to be able to select from. In each shift the Executive chooses which shift should be scheduled, the earliest and latest hours employees can be scheduled, and business hours. The earliest time for each day is used to set a 24 hour day. I already have this part taken care of.
In each shift, the business will be able to put in sections only if the shift is selected to be used in Business Hours. Using the information gathered from shift information, a series of questions for the section is asked to the section name, day required, number of employees needed earliest/busiest/latest, earliest/latest time employee can be scheduled in the section, and earliest/latest time of busiest for section. I already have this part taken care of.
This leads to the question I am stuck on. From the Section information for each shift, I need to be able to display all items for Bus_UUID on a web form that shows section name (This part is taken care of, but I don't mind changing how if needed). What I need is to be able to have it in the database which sections the employee should be scheduled. On the display it is showing as checkboxes. If checked, I need the information to be entered in database under employee ID/Bus_UUID. If unchecked, I need it to be able to be deleted. How do I do this in a database?
Section information for each business is liable to be different. My intent is to allow up to 25 sections per business (if more is needed they will need to call me).
Second part of this question: I am creating a place in which business have the opportunity to have set hours for employees (businesses using this could save schedulers a tremendous amount of time). For example, say full time employees work 1 month in days and the 2nd month at nights, then repeat after this. With this being set hours, it is needed to know which section the employee will be working in. The selected checkboxes from employee section select needs to be able to be checked here (up to five sections in a week). If checked here, shouldn't be able to be removed from Employee Section Select. How do I do this in a database?
After I have this answer, I will be able to use it for Scheduler Shift Select/ section select (in which all 4 shifts if selected in Business hours will be available on one form along with all section information for shift).
The tables primary key for Employee Section select is a BIGINT and can be used to record, but needs to include Bus_UUID in selection.