Relational data questions

Hello all,
I have a project i am working on and need some help.

The project is a check-in/out system using php/mysql
I have 3 different types of equipment, 6 different job types and depending on your job type the database needs to pull a specific device type and assign it to you as well as select and display a locker number and combo that the device is in. this project is still in its infancy and i dont have much done other than some abstract tables built.

My Main question is what would the best approach be as far as the table structures.

the data i would need in the tables are:
HardwareID - example= DH43289762
Type - example= Handheld, Wearable, Tablet
Scanner Included - example= Yes, No
ScannerID - example= SC100
Status - example= 0(available), 1(Checked Out), 2(Out for Repair)
LockerID - example= 001
LockerCombo - example= 1234
JobID - example= 1, 2, 3, 4, 5, 6
JobName - example= Stocker, Picker, Clerk …
Eid - example=JJones, J123456
CheckOutDate - example= 12/12/2017 13:23:56
CheckInDate - example= 12/12/2017 21:42:25

This project will live on a dedicated machine running xampp with Apache 2.4.29, MariaDB 10.1.29, PHP 7.2.0 of which the users will only be able to access the checkin/out page that will accept a EmployeeID and a checkin/checkout button

here are how i currently have the table structure, would this be efficient?

Lockers:

Equipment:

Users:

Checkinout:

Jobs:

forgot to mention that the checkinout table is where all the data will be stored, outs as inserts and ins as updates, but once a unit is checked out or in the equipment.estatus would be updated to reflect as well, also no rows would be deleted as we would need the ability to track and pull reports from this data.

You’re missing some consistensy on naming the columns. I like to use snake_case in the db and camelCase in code, but whatever you do it should be done consistently. I prefer singular table names, that doesn’t really matter though. I also strongly dislike repeating the table name in the column names. It’s all convention of course, I just don’t see the point of jobs.jobID, jobs.jobName etc. If I’m in the jobs table I know that “name” is the job name…

users table.
What does tm stand for in tmID and tmName? Can a user only have one job? I’d expect there to be a jobs_users table to allow a many to many relation between users and jobs

equipment table.
Type should probably be a separate table. What is rsinc/rsID?

checkinout
No reason to duplicate all the data from other columns here (tmName, hwType, lockerID, lockerCom). I’d probably call this jobs_equipment and have something like this in there
id
job_id (jobID)
equipment_id (hwID)
check_out_date (checkOut)
check_out_user_id (tmID)
check_in_date (checkIN)
check_in_user_id (??)

jobs
Depending on how you do things I’d consider adding who added the job, who ordered the job, when the job has to be completed, etc.

XAMPP isn’t supposed to be used for actual production systems - even on a local network. I recommend setting up a proper web server

Rereading your post

I have 3 different types of equipment, 6 different job types and depending on your job type the database needs to pull a specific device type and assign it to you as well as select and display a locker number and combo that the device is in.

Your database schema doesn’t include “job type” o.O

Hey Jim good to see you again! :stuck_out_tongue:

so let me add a few things to this,
This is more or less a proof of concept project not a production one, its purpose is to see if a tracking system like this is even viable for our environment. If this project does deem viable our software dev team would take this rough draft and mold/tweek it to fit their guidelines for trademarking and all the legal mumbo jumbo … all i am tasked with is getting some sort of rough/ugly beta test online no matter how it looks as long as its easy and streamlined to use.

Speaking of environment, let me explain a bit more about how this would be used. I work in the IT dept of a major distribution warehouse, here we have several different job types that use RF scanners to pick product for shipping and cycle product coming in and other jobs but i’m sure you get the point. as such our employees are hired for 1 position (job type) and that is all they do. so the many to many idea is not an important aspect of this as if someone was to move positions we could just go into the db and change their job id.

The equipment used here … there are only 3 major types of RF equipment that we need to track Tablets, Handheld and Wearable. The Wearable has a separate accessory that would need to be tracked with it as well and is the only one that has an “add on” so to speak and that is the Ring Scanner (rsid). Now the Job function will determine what type of device you are going to use, so picker would get a handheld and stocker a wearable/ring scanners ect …

now as for the use of this it will be loaded on a kiosk machine that only allows access to a single webpage. On that page would be 1 input field and a check in/out button. A user would enter their Team Member ID (JJones, G3219543) by scanning their badge (no keyboard will be connected to this machine) and click the button, then a box would popup giving them a RANDOM Locker Number and Combo to the correct piece of equipment for the job type assigned to them in the database. after they are done for the day they would then go back to the kiosk and scan their badge again and click the button this time the script would see that the user already checked out something and would check it in giving them a random empty locker and combo to put the equipment into. I may add scanning a barcode of the equipment’s asset tag id (hwid) when checking in. Now keep in mind there are several shifts of employees as this is a 24/7 operation and the same piece of equipment may be used/checked out 2-3 times a day as long as it was checked back in.

Now with all that said i also need it to store all the check ins/outs as we would need to have the ability to pull past data incase something is lost or damaged beyond repair we would have to look to see who was the last 1-20 users of the device.

I hope this cleared everything up.

In that case my table example is better as you have separate check in and out ^^

good luck with the project :slight_smile: throw out any questions if you come across any :slight_smile:

Article on your website, make the most of the information it is to read a lot longer.

Sponsor our Newsletter | Privacy Policy | Terms of Service