I need help in creating the following PHP MySQL system that will create a dock plan system. I do not know much about creating relational databases.

Let me give you abit off back ground on the basic structure of the problem.

We have deliveries on site five days a week and 24hrs a day.

Monday 06:00 to 06:00 Tuesday
Tuesday 06:00 to Wednesday 06:00
Wednesday 06:00 to Thursday 06:00
Thursday 06:00 to Friday 06:00
Friday 06:00 to Friday 17:00

The plan is done on a weekly basis, i.e week 1, 2, 3, 4, 5, 6, 7 ..... 44, 45

The current plan is in ms excel format that is on a shared drive but only in one location and other people cannot view it at other bulidings/areas.

The dock plan is split into days, delivery schedules from Monday to Friday into work sheets at the bottom.

I want to create a similar type of plan that is split down into days on a PHP and MySQL platform.

I want to have two section to this Admin and User.

Admin will have features to add, edit update and delete tables.

Users will only be able to add to the table the fields like Arrival Time, On Bay Time, Off Bay Time etc. also only to be able to update these fileds if they make a mistake.

MySQL tables I can think off is

Week No: List of week numbers
Arrival Time: - Times that the trailers are expected to be on bay
Bay Number: - Bay number the trailer needs to put onto
Route Number: - The route number will be linked to the supplier on this route
Supplier Name: - Vendors name is related to the route number

With no experience increating relational databases I cannot figure out how to create the mysql tables and join them with relationships.

The admin Area:
To create the following tables Initailly.

Week No: List of week numbers that will link all tables
table: id (Primary Auto int 11), week (varchar 3)

Arrival Time: List all the times from 06:00 to 05:30
table: id (Primary Auto int 11), time (varchar 10)

Bay Number: From 1 to 4
table: id (Primary Auto int 11), bay (varchar 10)

Route Number:
table: id (Primary Auto int 11), route (varchar 55)

Supplier Name:
table: id (Primary Auto int 11), suppliername (varchar 250)

Master Table: Holds all the data submitted by the user.

I can only think of the following fields required:

Master table:
table:
Plan Arrival Time
Bay no
Route no
Vendor/s
Carrier
Arrival Time
On Bay Time
Off Bay Time
Trailer No
Arrival Status
Bay Turnaround
Comments
Day
start Tip
Finish Tip
Stillages
Pallets
Labels
Modulars
Trailer Type
Shift User

and any other relational data from MySQL tables.

The supplier table should be able to link it to the other tables.
i.e.

Supplier Name: ..........................
Route No: .................................. (Drop Down List)
Time: ........................................ (Drop Down List)
Bay No: ..................................... (Drop Down List)
Week No: .................................. (Drop Down List)

These fileds need to be prefilled for the week day and Week number (users cannot change or amend this data in the master table).

As charts are need to view and measure the information.

I want to display charts on the website from the master table and also to be exported to an excel file and saved by a file name and week number.

i.e. The admin can view all the information added to the master table by the user and clicks a button (export to ms excel) and all the information is carried to the excel file.


Login Area:
When user logs in the record being updated or edit caries the users id so they can be identified who created and edit the records with name, date and time

Admin Login
Admin to create dock plan with certain fields to fill in for user ie like supplier and route number etc

manage users - edit, delete update status etc

Thank you.

Kind Regards

Qaysar