Simple Table Structure Clarification

[nickdawes28]

Hey Guys,

Have been given great advice on this forum in the past- I would like to quickly clarify a table structure for a database I am working on. I appreciate those reading this invest some time in trying to visualize exactly what the person posting is trying to achieve so I will try to be clear and concise.

I am creating a staff scheduler. The requirements are the following-

Company operates daily- each day consists of many trips, which in turn consist of many clients and many guides assigned to lead the trips. Guides are not available for work every day.

I would like to create a database to manage this system. A single user should be able to assign multiple trips to a single day, client numbers for these trips and multiple guides to each single trip. If guides are not available to work on a given day, they should not be assigned to trips on that day.

I am not sure if the following is as efficient as could be, or even correct for what I want to achieve. I would appreciate some feedback!

https://www.dropbox.com/s/xkdwphg4hf1qvdh/relationships.jpg?dl=0

 

[nickdawes28]

108 views... hoping somebody can offer some advice soon!

 

[jdraw]

nick,

I took a quick look. Some thoughts.
-Client table? And ClientTrip table?
-seems A Trip requires 1 or more Skills
-GuideSkill(s) must match TripSkill(s) to do the actual schedule
-GuideWithSkill(s) must be available in order to Schedule.


I think there is some analogy with Employees and Positions

Position has a Skill/Education requirements
Employee possesses some Skill/Education
To be promoted to a Position, that Employee's Skill(s) must match the Position's Skill(s) requirement.


Hope this is helpful.

 

[nickdawes28]

nick,

I took a quick look. Some thoughts.
-Client table? And ClientTrip table?
-seems A Trip requires 1 or more Skills
-GuideSkill(s) must match TripSkill(s) to do the actual schedule
-GuideWithSkill(s) must be available in order to Schedule.


I think there is some analogy with Employees and Positions

Position has a Skill/Education requirements
Employee possesses some Skill/Education
To be promoted to a Position, that Employee's Skill(s) must match the Position's Skill(s) requirement.


Hope this is helpful.

Hey jdraw,

Thanks for the response. I have taken a look at the tables again based on your suggestions- could you please explain in a little more detail what you meant?

I could create a client table, however I am not sure why this is necessary? The "ClientNumbers" field is a unique attribute to a trip, and would never need to be modified outside of the "trips" table. The system does not collect information regarding individual clients, just the total number for each trip.

I am not sure why it appears a trip requires a certain skill to operate? The daily_schedule table would look like this.

scheduleID tripID guideID
1 1 1
2 1 2
3 2 3

etc.

Hope you can clarify your points- I'd appreciate it!

-Nick

 

[jdraw]

If "ClientNumbers" field is a unique attribute to a trip, why not call it TripNumber?
Do you have to know which Clients are scheduled for or took a certain trip?
You indicated whether a Guide has a Skill or not. What is the rationale for knowing the guides' skill(s) if not for some factor of a trip?

 

[nickdawes28]

I think the field name "TripNumber" implies some sort of ID number for the trip- not what I'm aiming for which is the total number of clients on a given trip. So ClientTotal or something similar would be more descriptive- that's the logic I'm working with at least.

Nope, no need to know anything relating to individual clients- more of a staff scheduler than a booking system.

My bad- yes I would like to factor in guide skills to trips. I'm not sure how to use the table structure to validate this. Any advice?

Thanks again.

 

[jdraw]

Agree with TripTotal, or TripPersonCount or similar.
Your model suggests that a Trip can have different names (Trip_Names).
Can you give a sample with some data to help readers understand your set up?

 

[nickdawes28]

Absolutely.

trip_names (tripNameID, tripName)
1 Half Day
2 Full Day
3 Heli Hike
4 Climbing

trips (tripsID, tripsTripNameID, tripsDate, tripsClientNumbers)
1 1 01/08/2015 20
2 2 01/08/2015 12
3 3 01/08/2015 4

daily_schedule (scheduleID, tripsID, guidesID)
1 1 1
2 1 2
3 1 3

guides (guidesID, guidesName)
1 Bob
2 John
3 Steve

guidesSkills (skillID, skillName)
1 Bus Driver

guideSkillsIssue (skillID, guideID)
1 1

working_days (working_dayID, working_day_date)
1 01/08/2015
2 02/08/2015
3 03/08/2015
4 04/08/2015

guide_schedule (guideID, working_dayID)
1 1
1 2
1 3
1 4
2 1
2 2
2 3
2 4
3 1
3 2
3 3

Hope this format is readable, if you would prefer something else please let me know. From the above you can see (as an example) on the 1st August there are 3 tours, with a guide assigned to each one. One guide is a bus driver, and has that skill assigned to them.

 

[jdraw]

Attached is a draft for consideration. Trip session is intended to be an "instance" of a Trip.
This is much like a teacher teaching Geometry --- Geometry is the Course -- but Thursday 10-11AM Room 302 in the "Kings building" is the course session.

Hope it's useful.

 

[nickdawes28]

Thanks jdraw, interesting to see your take on this. One thing that springs to mind (if I have interpreted your diagram correctly) is that the TripSession table would have a "ClientCount" entry every time I wanted to assign a guide to a trip.

For example,
TripSession (TripSessionID, TripID, WorkDayID, GuideID, TripSessionTimeStamp, TripClientTotal)
1 1 1 1 timestamp 22
1 1 1 2 timestamp 22
1 1 1 3 timestamp 22

Would it make more sense to place the "TripClientTotal" field in the Trip table? The trip table would contain the TripID, TripName and a ClientTotal. The TripSession table would hold the TripID, GuideID, WorkDayID and timestamp.

Let me know your thoughts?

Thanks.

 

[jdraw]

You know your requirements and business. I'm treating Trip like a template. There may be more fields/attributes for any/all tables.

I don't know what your Heli... was but let's consider a Helicopter trip/tour.

As a template, there will be a helicopter, necessary skill is qualified Pilot, some starting point and an itinerary.

When you get to TripSession --you know it's a heli trip, but you may change the itinerary for any individual session, and you might use different pilots/guides...etc.

As for TripClientTotal, you may have a min and max number for a Heli trip(depends on size of helicopter). It may not be important to a Walking Tour?? If you have Min and Max number of participants for a type of Trip, then that could be an attribute of Trip.
The actual number of participants in a session would be held in TripSession, as I see it, and would have to be between the Min and Max count.