Multiple Unique Properties to a table's index (1 Viewer)

Manos39

Registered User.
Local time
Today, 00:29
Joined
Feb 14, 2011
Messages
248
Hi, i am trying to make a simple DB for scheduling tennis courses ( course, instructor, court, date and time ) and that info
is held in a table Tbl_CourseSessions
Need to index the table properly and I am stuck because i cant think more of how to ensure that, because
otherwise there will be duplicate records
Need to ensure that:
A) same day and time an instructor can not instruct (course is irrelevant - same course can be instructed by others by another same time same day)
B) A court cannot be chosen same day same time (of case course doesn't matter)

i have read this article http://www.geeksengine.com/article/composite-unique-index-access.html
but i managed to make similar but more simple conditions happen in cases where one instructor
cannot be double registered in a year
This is something more
 

Attachments

  • 1.PNG
    1.PNG
    17.1 KB · Views: 189
  • Tennis.accdb
    1.1 MB · Views: 174

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:29
Joined
Feb 28, 2001
Messages
27,001
I think it might be better if you broke out the court information to a separate scheduling table that has court + date & time as a two-field PK so that no two records can have the same court and date/time values. If you want to put an ID on this court schedule record to link it to things that might need to use the court, that would be OK. It would be OK to have a foreign key to the person scheduling it, perhaps, but it doesn't matter who holds the reservation. It matters that it is held.

Ditto for the instructor + date & time. And, of course, you would use the same approach to keep from overbooking the student. Then you can link the student, instructor, court, and course to the "scheduled" IDs from each of the court, instructor, and student tables. The key here is knowing what is independent data and what is dependent and what is extraneous.

OK, the JOIN to regroup all that stuff into a single record for reports gets messy, but in your original description of the problem, you were driving yourself nuts trying to do everything at once, when your better approach would be "divide and conquer."

Now... WHY were you driving yourself nuts here? (Rhetorical question) Because you were bringing in extraneous data to the task at hand. Like I said, doing too much at once. You don't CARE about court ID when scheduling a student or a course, because any student can take any course on that court - but then it is booked and no other student or course applies at that time. In other words, the scheduling of the court is a thing unto itself and no other data needs to be considered. Ditto for the student, who can only be scheduled for one thing at a time. Ditto for the instructor, who can only be schedule for one duty at a time. Keep the scheduling pure by excluding data extraneous to the act of scheduling components of your data set each of which must be single-use.

Now, it is ALSO possible that you CAN do it in one table (such as your CourseSessions table) by having three unique two-field indexes - an index that combines instructor with date/time, one that combines court with the same date/time field, and one that combines student with the same date/time field. But to my way of thinking, you just made your scheduling and reporting process more complex that way, just as my suggestion for splitting scheduling into separate tables adds a different kind of complication. But the point is, when you have extraneous data to the question at hand, don't let it complicate your life. Separate the process into its essential pieces, handle them separately, and rejoin them later.

Edited by The_Doc_Man for typos and a minor correction.
 
Last edited:

Manos39

Registered User.
Local time
Today, 00:29
Joined
Feb 14, 2011
Messages
248
Thank you but It would be easer now to keep on to what i already have as a structure.. i simply need to see how to do it. Could you help me on the specific table? I think i have divided information and cases in that db already in too many pieces..
 

Manos39

Registered User.
Local time
Today, 00:29
Joined
Feb 14, 2011
Messages
248
I thinking to have a subform to a form that holds planning Sessions and be helped on scheduling by seeing specific date and time what does instructor and which court is occupied. Just need the indexing to be helped..
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:29
Joined
Feb 28, 2001
Messages
27,001
The indexes are HOW you enforce the scheduling process. If you place a unique index on court ID and date/time, that stops duplicate scheduling of a court. Whether you do that in your CourseSessions table or a separate table, the unique index on ID and Date/Time prevents duplicated scheduling. The same principles apply to instructor + date/time and to student + date/time. The course ID doesn't matter that much. Oh, it is important, in fact it is why you do this, but it doesn't affect scheduling in a way requiring uniqueness. It is the independent variable of the overall process.

Search this forum (SEARCH is in the upper right of the top line) for "Scheduling" and "Booking" to see how folks approach the problem of making a reservation-style schedule for a facility. We have probably a couple of hundred individual threads from the last few years and I wouldn't be surprised to see thousands of articles in the last couple of decades. Some of the articles will include ways to display schedules so you can see what is available.

As to your comment "I think i have divided information and cases in that db already in too many pieces.." I cannot disagree with you. I see the following as how I would do this,

Courts - court ID, location
CourtSched - (court ID, date/time), type of activity: (teaching, open play, maintenance, lights-out/curfew/facility closed), who scheduled

Students - student ID, describes the student, course history (could be a child table)
StudSched - (student ID, date/time), activity: (specific course, practice session, league play)

Instructors - instructor ID, describes the instructor, some indication of courses this instructor CAN teach - could be a child table
InstSched - (instructor ID, date/time), activity (such as course ID)

Courses - course ID, description of course
CourseSched - courtID, studentID, instructorID, date/time, courseID

The (xxx,yyy) is a unique compound index such that the combinations cannot be duplicated even though the individual values can - and will - be duplicated frequently. If you maintain the individual component schedules, you can reference them later with foreign keys.

You would build a form with three sub-forms for scheduling the court, student, and instructor. The main form holds the final part, the course which is the actual thing to be scheduled. The trick, of course, is to find a date/time where a court is NOT scheduled and a student is NOT scheduled and an instructor is NOT scheduled (all simultaneously.) Since Access typically has sparse tables (i.e. there is no record if there is nothing scheduled - as opposed to a blank record representing "nothing scheduled"), this search might be tricky.

There is also the question of whether for a given course it is possible to be multi-day, where the student is assigned a given instructor for all of the days of the course. If that can happen, there is another can of worms in the mix.
 

LarryE

Active member
Local time
Today, 00:29
Joined
Aug 18, 2021
Messages
562
What it sounds like you have is:
  1. Courses taught
  2. Which may be taught by various instructors
  3. Which have various students in each course
  4. Each student may be assigned various tennis courts for instruction
  5. On various days
  6. At various times of the day
Is that correct? Because if it is, then no amount of advice on "indexing" will help you because your application is not built with a proper foundation of tables and fields to begin with. You appear to be trying to use ACCESS as you would a spreadsheet. Not a good idea. You are building a house. Your foundation is quicksand at this point. In my opinion, you will need 6 tables:
  1. A Course table
  2. An Instructors table
  3. A student table
  4. A Tennis court table
  5. An Instruction Date table
  6. A Time Of Day Table
Once your tables are established with Primary keys connected with foreign keys in your tables, then and only then, is "indexing" an issue. Until then, indexing is not an issue. Build it properly from the beginning. Then worry about indexing.
 

Manos39

Registered User.
Local time
Today, 00:29
Joined
Feb 14, 2011
Messages
248
In order to manage changes i got lost because i ve already had reached to finishing forms.. These are db relationships and i could use some advise in making some changes on these tables... lets say Tbl_CourseSession' s instructorID should get values from Tbl_CourseInstructors / (instructorID)
also there is Tbl_CourseAthletes which could be assosiated with Tbl_CourseSession also in the thinking of your opinion ... am i wrong?
 

Attachments

  • Relationships.pdf
    140.8 KB · Views: 177

LarryE

Active member
Local time
Today, 00:29
Joined
Aug 18, 2021
Messages
562
I downloaded your tables. You have some tables you don't need. Some of the fields in them belonged in other tables so I moved them where they belong. Also, I moved the fields in the Tbl_AthleteSubscriptionTypes table to Tbl_Athletes but I am assuming each athlete has only 1 subscription type. I re-designed the relationships also. Here is the resulting Database with the changes. It is much less complex than you had.

Also, I noticed that academic year was a separate field. It does not need to be a separate input field. The Course table has new BeginDate and EndDate fields. You should input the course Begin Date and End Date into these fields. Then, the academic year can be extracted from those dates using the Year() Function provided in ACCESS. You can then create an academic year field on forms and reports using the Year() Function.
 

Attachments

  • TennisDB.accdb
    1.3 MB · Views: 188
Last edited:

Manos39

Registered User.
Local time
Today, 00:29
Joined
Feb 14, 2011
Messages
248
I downloaded your tables. You have some tables you don't need. Some of the fields in them belonged in other tables so I moved them where they belong. Also, I moved the fields in the Tbl_AthleteSubscriptionTypes table to Tbl_Athletes but I am assuming each athlete has only 1 subscription type. I re-designed the relationships also. Here is the resulting Database with the changes. It is much less complex than you had.

Also, I noticed that academic year was a separate field. It does not need to be a separate input field. The Course table has new BeginDate and EndDate fields. You should input the course Begin Date and End Date into these fields. Then, the academic year can be extracted from those dates using the Year() Function provided in ACCESS. You can then create an academic year field on forms and reports using the Year() Function.
Thank you for helping but i am thinking that:
a) There is no purpose for connection (especially one to many) between club details wish courses ? (DB is for only one club or wasn't it said? )
b) There was purpose to have Tbl_AcademicYears because an athlete in a year could be skilled so there would be choice for upgrade to higher level in a year
c) Said that shouldn't an athlete be connected with Tbl_Courses having a table Tbl_CoursesAthlete (with fields DateFrom - DateTo?) ..and since this is the case why move AthleteSubription fields to Tbl_Athletes (a subscription has to do with Course)

d) Same conditions i find in the case of Instructors
an instructor can be in the middle of an academic yeat in a course or hired in the middle of a year so..Tbl_CoursesInstructors - And InstructorYears i think is needed
e) I think it is a mistake to have athleteId in the Tbl_Courts, I think it should be connected with Tbl_Courses (courseID)

Moreover an athlete would not have an instructor (you fitted instructor in TblAthletes) since in case he she is not present another instructor teach (the class athlete is in)
..and how about bad data ...
I like though and you gave me a good idea of: Having first plan a session by Court because as i see you have Tbl_court connected One to Many of Tbl_CourseSessions..
Thanks
 

maybiler1

New member
Local time
Today, 08:29
Joined
May 7, 2022
Messages
2
I wouldn't say that it is syntactic sugar exactly. The primary key is the preferred or default key for the table. It is a convenience feature that allows the designer to distinguish the preferred key from among perhaps several other unique non-nullable keys.
 

Users who are viewing this thread

Top Bottom