Update - multiple tables

[Kerry Purdy]

Hiya

I have a database which, among other things, records training courses
scheduled and their requirement details. I use a few tables to store the
data:-

Tbl Schedule = Primary Key TSID, Start Date, Duration (days), course title
etc.
1234, 6/11/06, 4, Excel Intermediate

Tbl Rm Req = TSID, Date, Room Reqd
1234, 6/11/06 - Room1
1234, 7/11/06, Room1
1234, 8/11/06 - Room2
1234, 9/11/06 - Room2

tbl cat = TSID, Date, CatRes
1234, 6/11/06, Buffet
1234, 7/11/06, Pub
1234, 8/11/06 Pub
1234, 9/1//06, Buffet

I would like to add the functionality of changing the date (the duration
will not be changed and could range from 0.5 days to 5 days).

Currently the user changes the start date in the training schedule form,
then has to manually change the date next to each room requirement and then
again for each catering requirement.

I would like to be able to change the date in the training schedule form and
have the room requirement and catering requirement dates update automatically.

The main problem is that in the schedule the dates are only stored as start
and duration, in the others each date is stored. I am assuming that this may
involve some coding, which is fine but wasn't sure where to put this question.

Thanks very much for your time.

Kerry

 

[Jeff Boyce]

Kerry

From your description, it seems like you are using the TSID (?Schedule ID)
as a primary key for your Room and Catering tables. I would think that the
relationship between a Course and a Room (or a Course and a Catering choice)
would be many-to-many, not one-to-one, as implied by them having the same
ID.

If the relationship is many-to-many in the real world (i.e., one Course
could use multiple Rooms, one Room could be used by multiple Courses), you
need three tables, one for Course, one for Rooms, and one to resolve the m-m
relationship.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Kerry Purdy]

Hi Jeff

Yes, I have 3 tables
Tbl schedule = TSID Primary Key
Tbl Rm Req = TSID Foreign Key, RmID Foreign Key
Tbl Cat Req = TSID Foreign Key, CatID Foreign Key

One schedule could use many rooms or just one for the duration of the course
(a 4 day course could use 2 of our rooms for each day, so 8 room records
could be entered against the TSID)

One schedule and date could have many catering requirements, pub lunch,
buffet lunch, refreshments, breakfast pastries etc. it depends on each
indivbidual schedule added.

The detail I have entered in my explanation is very basic, the schedule
table and the database in it entirety is much bigger than my example with
many more fields and lots more tables and relationships. I didn't feel they
were relevant to my question so I kept it short and sweet.

Thanks very much for the advise. I am happy that my relationships are set
correctly and work correctly.

Kerry

 

[TedMi]

You table structure is not normalized, because it repeats data (start date)
across several tables. Non-normal data causes the very problems you describe.
I would recommend the following structure:
tblCourse: CrsID (PK), Days, Title, Prerequisites, etc,

tblRoom: RoomID (PK), Capacity, AVequip, Location, etc.

tblSession: CrsID, StartDate, RoomID, Catering
Compound PK StartDate, RoomID
non-unique index on CrsID

Relation from tblCourse to tblSession on CrsID
Relation from tblRoom to tblSession on RoomID

This structure will also allow you to keep track of teachers and students,
whom you would assign to Sessions, not to Courses.

 

[Kerry Purdy]

Hiya

I am pretty much there then. I have the following tables, sorry, I just
avoided entering all the detail to keep it simple. My database also caters
for room hire requirements and bookings for courses on the training schedule
too.

I can see your point though so let me detail my database in full just to
make sure - as I am very reluctant to change the design at this stage -
everything is working perfectly (well, other than the automatic date change
functionality). I appreciate your advise though so please feel free to pick.

Tbl Training Available:- TAID (PK), Name, Version, Level,
Tbl RmDet:-RmID (PK), Room Name, Capacity, Type, Cost
Tbl CatAv:-CatID (PK), Catering item Name, Recommended Cost PP
Tbl ResAv:- ResID (PK), Resource Name, Rec cost per day
Tbl ClientData:-CID, Client Name, contact name, Address 1.....6

Tbl RmHire:-RHID (PK) Start Date, Duration, Booking taken by, Provisional,
confirmed, cancelled, CID(FK), RHCost

Tbl Schedule:- TSID, Start Date, Duration, TAID(FK)

Tbl TSBkg:- TSID(FK), TSBID(PK) CID, Booking Cost, Provisional, Confirmed,
Cancelled

Tbl TSDelData:- TSBID(FK) Del Title, Del First Name, Del Surname, JI Sent,
Attended, Cancelled, Evaluation Completed

Tbl RmReq:- TSID(FK), RHID(FK), Date, RmID(FK)

tbl CatReq:-TSID(FK), RHID(FK), Date, CatID

Tbl ResReq:-TSID(FK), RHID(FK), Date, ResID

I hope this explains my system a little better, I did have a real headache
trying to decide on the design and the relationships as there are so many,
many to many relationships whichis why I created so many interim tables. As
I said, this system, although you may not agree with the design does work
perfectly but I am happy to hear your views, it is currently filled with 5
months worth of data so any design changes could, if really necessary, be
managed.

Thanks very much.

Kerry

 

[TedMi]

Kerry: Your design is good, for the most part. The only problem I see is with
the last 3 tables you mention, xxReq. It appears the all 3 of them specify
attributes for the *same* entity, identified by TSID, RHID, Date (that's what
I called "session" in my example design). Therefore, I would make the RmID,
CatID and ResID fields in *one* table. That way, the date will appear only
once per session. Now, if there are different requirements for each day of a
session, I would identify those as follows:
The Date field will hold the Start Date in all records for a session.
Add an integer field DayNum which runs from 1 to Duration, making it part of
the unique compound index (TSID, RHID, Date, DayNum).
For queries and reports, you can display the dates of each day of a session
by a calculated field, DateAdd("d", DayNum-1, StartDate). That way, it
becomes self-adjusting if the Start Date is changed.

 

[Kerry Purdy]

Ted

Fab, that works fantastically. I went down the DayNum route. I have
created my update queries, I have tested it a few times and all seems well, I
just have to add it to the on_dirty? or whatever routine used when a date is
changed.

Excellent. Thanks very much indeed for your time.

Kerry