Need expert advice on database design

[jdnewton]

Hi,

I am familiar with only the basics in Access and feel some sage advice before diving in would be welcome.

I aim to set up a database with the final result being the ability to produce a staffing rota on a week by week basis.

There are three main elements:

1) List of which team covers which area on each day - this is constant

2) List of which staff members are in each team - this varies every 3 - 6 months

3) List of which staff members have other commitments - leave, on-call, not available - this is a day by day rota.

So, as an example:

Team A covers Area 1 on a monday morning
Joe Bloggs is leading Team A
John Doe is in team A this month
John Smith is in team A this month

And for this week:

Joe Bloggs is on call on Wednesday
John Doe is in team A but on Annual leave
John Smith is in team A and available


The final output will be:

----------------------------------------------------

For week commencing 01/03/08

Monday Tuesday Wednesday Thursday Friday
Joe Bloggs Joe Bloggs Joe Bloggs Joe Bloggs Joe Bloggs
John Smith John Smith John Smith John Smith John Smith

On call On call On call on call On call
Joe Bloggs

Away Away Away Away Away
John Doe John Doe John Doe John Doe John Doe

--------------------------------------------------------

So, constructing the tables to hold the data is easy - but what is the best way to organize them and to set up relationships?

Also, should the output but as a report or as a query? In the end I would hope to have a form front end where people can choose the week on a calendar and the schedule is produced, perhaps also with the ability to flag up deficient areas.

I expect this is a reasonanbly simple project but I would like to get my head round the concept and layout before embarking.

All help greatly appreciated.

Jim

 

[KenReay]

Hi

have a look at

http://www.access-programmers.co.uk/forums/archive/index.php/t-109801.html

it may help

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[fneily]

constructing the tables to hold the data is easy" - I like your sense of humor. That's THE hardest part. Alot of folks that try Access don't normalize their tables. Study
Fundamentals of Relational Database Design

http://r937.com/relational.html

and read some works by Ted Codd. If the tables are constructed correctly, forms and reports and queries are the easy part. The only questions left should be ones on coding.

Since I can only work with what's presented, some assumptions from your post and final output example:
One Area is maintained by ONLY one team and one team ONLY maintains one area. So a one-to-one relationship.
One team has many staff and one staff member can be assigned to ONLY one team. So a one-to-many relationship.
Areas are done everyday(as per your example), Monday through Friday.
You are scheduling everyone in advanced.

Tables: They can all be pre-filled.
tblArea fields= AreaID, Desc.
tblTeam fields= TeamID, Desc., AreaID
tblStaff fields= StaffID, then info fields of person
tblReason fields= ReasonID, Desc. (Leave, On-Call, etc.)
tblWeekAssignment fields= StaffID, StartDate, TeamID, ReasonID, Leader(a checkbox)
(primary key multi-field of StaffID/StartDate)

Now you can have a form that has maybe the Calendar Control on it to pick a date. This'll feed a query that has the tables connected to retrieve your data. So a date is fed to a query, it's matched against tblWeekAssignment to get all the people working that week and which teams they're on. Through TeamID, you can get the Area's Name. From the checkbox, you can see who's leader for that team. Through ReasonID, you can get any status of a person.
Feed this query into a report. Create Labels for the days of the week and then just place the fields underneath them in the detail section. If you do a page break on Area(or Team), you can produce a weekly report for all the areas in one shot.

It looks like it'll work. "easy". Yeah, right.

 

[BobRodes]

<constructing the tables to hold the data is easy

I think what the OP is saying here is that anyone can put a table together, but how do you set up relationships and the like properly.

JD, you might indeed like to have a look at the links provided. However, let me give you a bit of insight as to how fneily came up with his structure.

Relationships need to be in terms of one-to-many. The way to relate two tables is to add the primary key of the table on the "one" side of the relationship as a field in the "many" table. This is called a "foreign key." Note the ReasonID field in the tblWeekAssignment table.

If you have a many-to-many relationship (you don't here), you need to resolve it with an "associative table" that is in one-to-many relationship with each. An example would be a library database with books and patrons. One patron can have multiple books checked out; one book can be checked out multiple times. A "loans" table would resolve this: a loan is the loan of one book to one patron, but a patron can have many loans and a book can be loaned many times.

While it is a good idea to know database theory, I personally found that I created numerous properly-normalized database applications early on by simply following the above rules, and without knowing anything further about design theory (including the word "normalize"). However, with a complex database design, the theory becomes increasingly useful in working out the correct logical design.

I personally wouldn't separate Area and Team into two separate tables, by the way. I'd just have a Team table, with TeamID, TeamDesc, and AreaDesc. Fneily's doing this because they are two separate entities, and sometimes it makes sense to relate one-to-one entities in separate tables like this. In this case, it's kind of a tossup, with me coming down on one side and I on the other.

Bob

 

[fneily]

BobRodes - I could debate the use of normalization in simple databases, but I won't. The Area table I suggested may or may not be used as you showed. I have it because in real-life, I had Vice Presidents decide to change names of divisions, districts, areas, etc. So having a table that just holds the description means you only have to change it in one place. If AreaDesc is used as a common field, then you'd have to do a mass change in the table, in criteria fields in queries, etc.

 

[BobRodes]

<but I won't
Me neither.

<If AreaDesc is used as a common field
I don't think I follow your line of thought here. I'm only seeing AreaDesc in one place too in this case, given that there's only one area per team and one team per area. I don't understand why, if someone changed the name of a Team, it would require any change to an Area field or vice versa, and I don't understand why you'd have to change the field name in a query either if there were changes to the data. Can you give an example of some sort of change where your structure would be more efficient than the one I suggest? Again, I'm not saying yours is wrong, just that I don't see how it's an improvement over mine.

 

[deekydoo]

After many years of designing and actually making database management systems work with ever changing requirements, I go with fneily all the way. The more normalised your tables are, the easier it is to adapt the database to handle ever changing requirements. From what I've seen it seems to me that most database project failures occur due to the flattening of the table structures.