New Project...wanting to know what I'm getting into

[1jet]

Hello
I never knew how addictive/fun access databases could be. Many thanks to this forum and the gurus. You all know who you are!

My first project was a basic timekeeping system for a mate's firm.
He's able to find the times each employee's spent on different projects. Giving him costs and time spent reports, also storing info on his employee etc etc.
Next it got a little easier with what I've learnt from the first.
Just created an employee record database for my father for his employees.

Now I'm wanting to create something I feel is going to be VERY difficult.
Bascially my friend owns an indoor volleyball warehouse with 8 courts.

I'm wanting to create a total DBMS for his business. Where he can automate weekly game fixtures for each week, for every division, for every season (see how a noob like me can get stuck so quickly?)...oh also to keep records of team points during the season for rankings and finals purposes.

It's already become very hectic and I dont want to give up.
Hectic for me...maybe not for you guys...please help me out.

I expect many to ignore this thread and move onto the next...but if you;re willing to throw in ideas, ill keep this thread going posting up pics of my relationships, table structure etc as I go along.

Ta!

 

[Singh400]

I guess. Basic structure would be:

tblCourts
tblDivision
tblSeason
tblTeams
tblGames

I would start with lots of sheet of A4 and pens. Draw it out before you start doing anything.

How does he currently keep track of all of this?

 

[1jet]

wow I didn't think anyone would reply this thread
im hoping there'll be many ppl wanting to participate
anyways ive done some work with some tables and relations...im thinking theres a problem
but I CANT FIND IT!

well ill still be working away on this....cheers fellas

By f22a at 2008-11-07

 

[WayPay]

I see some problems with this:

1. The name tblEXPAND. All other tables are named for the data they contain, this one is not (maybe I'm unaware of the noun Expand). I'd call it tblBooking or tblReservation or something like that.
2. Both Day ID and Season ID in tblEXPAND. Things you can calculate/derive should NOT be in a table, and I think you should be able to derive the season from the date (Day ID). Come to think of it, why is there a Day table at all?
3. If tblEXPAND does contain reservations, it looks like only Teams playing in a Division can rent the courts. If this does not hold true, that's a problem, too.

BTW you're doing quite well for a self-proclaimed noob .

 

[1jet]

I see some problems with this:

1. The name tblEXPAND. All other tables are named for the data they contain, this one is not (maybe I'm unaware of the noun Expand). I'd call it tblBooking or tblReservation or something like that.
2. Both Day ID and Season ID in tblEXPAND. Things you can calculate/derive should NOT be in a table, and I think you should be able to derive the season from the date (Day ID). Come to think of it, why is there a Day table at all?
3. If tblEXPAND does contain reservations, it looks like only Teams playing in a Division can rent the courts. If this does not hold true, that's a problem, too.

BTW you're doing quite well for a self-proclaimed noob .

lol i did a unit introducing databases at uni once upon a time
but that unit doesnt make anyone an expert....only patience, experience and flexibility...im trying to make access databases a side hobby now...

anyway
1. tblEXPAND will be renamed or tblFixture...i just couldnt think of a name at the time
2. I had a thought about tblEXPAND and having Season ID in there, your probably right I can remove it. I may restructure tblSeason's relationship to the other tables, as I'm thinking game schedules should only be for the current season. tblSeason would have no real use except for storing historical team scores and rankings (??)...

Now for tblDay, I feel it's necessary even it can be calculated, unless I can be convinced otherwise. Here's my reasoning, and I'm open to discussion.
Currently....
Mondays - all time slots and courts are all men's divisions
Tues, Weds - all time slots and courts are all mixed divisions
Thurs - half the time slots and courts are open and women's divisions
(so that means half of thursdays and friday have courts available)

So in tblEXPAND, I feel DayID, SlotID, DivisionID are somewhat necessary when the time comes to assign teams on Fridays when there are more teams

3. It is true when teams sign up, they must choose a division to play in...if they really cannot choose, they'd be playing on thursdays in the open division

well feel free to throw any ideas opinions at me
cheers

 

[1jet]

just an update for those interested

By f22a at 2008-11-08

 

[Banana]

You're off to a good start, I think.

Few pointers.

1) You really do not need a Day table. You just need to store the date, and you can enforce the business rules via form (e.g. validate that the date is Monday for a Men Division reservation, etc)

2) You would not keep "Season Score"; this can be calculated from each game victory/defeat, and besides, this is more 'normalized' way to do this if you want to keep track of win-loss statistics as well.

3) Exactly what is "Slot"?

4) "Availability" in tblCourt... what does that represent? If you want to ensure that a court isn't double booked, one way to do is to force date/time reservation be indexed uniquely (or made a composite key) so there will be an error if court is about to be double booked.

In a sense, you want to validate data *before* you insert them, not *after* (which would be the case if we kept the Season Score and Availability because we have to monitor those and make sure they are correct whereas doing it before requires no monitoring on our part.

HTH.

 

[1jet]

Thanks for the feedback HTH
1) OK you're the 2nd to tell me this, so I had more of a think about it. If I removed tblDay and the field tblFixture.[Day ID], you're right, I wouldn't limit myself to only the number of Day's in tblDay. I think I'd need to change tblFixture.[Day ID] to tblFixture.[Play Date] correct?

2) You're right about this too. "Season Score" will be removed as it'd be easier to calculate and reduce unnecessary data.

3) Slot is the time a game is played. Each night there's 5.
- 18:15
- 19:00
- 19:45
- 20:30
- 21:15
I'm now thinking I should change these Slot values to text rather than time values eg. Game 1, Game 2 etc

4) Availability will be a Yes/No field. There may be times in the future when a court will not be free for social events or maintenance.

 

[WayPay]

The updated scheme looks better; the circular relationship worried me.

After your explanation, I do think you might need a Day table, but it's relationship would be with a CourtAvailability table. This CourtAvailability table would also be related to the Division table. You're going to want a full date in tblFixture at some point. Normalization theory is very clear about calculated values not being allowed, and for good reasons.

I don't think the Division should be part of the key for tblFixture; having it in the key means you can have multiple Divisions on the same Day, Court and Slot.

You just need to store the date, and you can enforce the business rules via form (e.g. validate that the date is Monday for a Men Division reservation, etc)

Be careful with 'enforce via form'. You might want to change the data in some other way in the future, and you'd need to re-implement the business rules from the other form. The database should reflect as many real-world constraints as possible. Some real-world constraints are impractical (maybe even impossible) to express in relationships, though.

Questions:
1. One team can play in multiple divisions at the same time. Is this right?
2. How many Teams to a Fixture? 1, 2, (1 or 2)? If more than 1, always in the same Division?
3. What historical data do you want to keep?

 

[1jet]

Hi all, I've deleted the previous relationship images from ImageShack without realising that it'll remove them from this thread too. Good news, the relations havent changed much despite excellent help from you guys...here's why...

By f22a at 2008-11-11

Since last week I've been stuck at how I'm going to link the teams from relationship 1 to the fixtures side (relationship 2). At this level I don't think I need to. My reasoning is...

Relationship 1 - will be dealing with storing team scores in their divisions in the appropriate season. It will also help keep teams in their playing divisions later on.

Relationship 2 - will be dealing with what courts will be played by what divisions on the appropriate days.

so...

How will I determine what teams play on which courts on certain times?
I've come to the conclusion that these will be form enforced rules.

To complicate matters even more....teams have certain playing preferences.....eg usually uni students doing mind what times they play....but older fellows who work usually dont like to play the early 6:15 or late 9:15 games

This is now what I'm going to figure out...

 

[gemma-the-husky]

just to go back to the beginning

do you want

a) a system to manage court bookings
in which case are bookings restricted to league players only, or can outsiders ring up and book a court, is there a different charge rate for different times etc

or
b) a system to manage leagues, results, games, players (do individual players have points scored, mvp type stuff etc)

do you want the system to generate fixtures
are there multiple leagues eg, mens/ladies/mixed etc


or even
c) both - since a) and b) are by no means the same thing

 

[1jet]

hi gemma-the-husky
i'd like to have both a) and b)

for a) bookings are for league players only and all rates at all times are the same
for b) there are no points for individual players so far...just teams

i think we mean the same thing when you say league and i say division....there are multiple leagues.....men's division 1 - 5...mixed division 1 - 5.....womens division 1 - 5