Problems applying many-to-many relationship 2

[CobaltWolf]

I must begin saying that I am a total n00b with Access, and practically zero knowledge of SQL language (which is why I'm trying to do this exclusively with what's available in the Access interface). I include here a screenshot of the relations diagram in Access:

The situation is this: a friend of mine is head of a local basketball referees club, and he needs something that helps him manage game tournaments. He would need to keep a contact list of the manager(s) of each tournament, assign games to each tournament, and assign referees to every particular game (each game has more than one referee). Some games can be suspended, and referees could be re-assigned to other games, so I have to take into account that too. I'm trying to do that in Access, and then create user-friendly forms so my friend doesn't have to deal with the program itself.

[Queries would be later used to determine stuff like how much the club owes to each referee (number of games assigned multiplied by game fee), make reports on the games assigned to each referee (say, on X month or range of months), etc. etc. But this is all wishful thinking on my part so far since I haven't been able to move past the creation of the tables.]

I've searched the web, consulted one book, and what I basically have found has been instructions on how to create the relation shown on the diagram above, and thats it. I haven't been able to find anything on HOW to apply many-to-many relationship in the forms, so that I can link games for to referees and referees to games...

I've easily managed to create a form to enter a record of each referee (that worked fine and I have filled a table with all of them), and experimented with a Tournament form where I could enter the games for each tournament in a sub-form (that apparently worked), but I have no idea how to assign referees to each one of those games. I guess it would be ideal if I were able to select the referees in that subform as I enter the games... but maybe that would complicate things, I don't know.

I tried to be as thorough in my description as possible, but if you need more info, feel free to ask. Any advice or links would be most welcome.

Frustratedly yours,

Ed

 

[fneily]

Warning! Access is a different beast then anything you came across. It is a Relational Database with non intuitive concepts. I would advise taking many Access courses. And try to find one that will show you Normalization, a concept you'll see later is important.
Some assumptions since you did not specify:
A single Tournament can change sites at later dates.
A single Tournament can have games at different locations, like the Olympics.
A single game can have a different price in a different tournament.

Let's look at your tables. First Normal Form states that any non-primary key field should be related to the primary key and only to that key. So, in the managers table, take out TournamentID. In the Games table, take out TournamentID, GameDate, Location, Played. In the Tournament table take out Location, # of Games(this is a calculated field. Calculated fields are not stored in Access tables), Price. These fields do not SOLELY belong to just that primary key or table.
You have, as you stated, some many-to-many relationships, which all relational databases dislike. So, you will build what is known as a junction table. Call it TourGame, or something. In this table, you'll have the primary keys of the tables you want to connect AND ANY COMMON FIELDS.
This table would then have the following fields:
TournamentID, ManagerID, GameID, RefID, Date, Location, Price, Played
The primary key is a composite key of TournamentID, GameID, ManagerID and RefID.
All main tables can be pre-filled. You'll have an entry form for the TourGame that has comboboxes for the ID's to speed entry.
If you look at this table, a Tournament can have one or many managers(as you stated), can have one or many games(as you stated), each game can have one or more refs(as you stated) and it'll track if the game was played or suspended(as you stated). And things like what is owe to each ref can be calculated.
My opinion.

 

[fneily]

Oops. Made a bad mistake. It's not the first normal form that explains the relationship between the non-primary key fields and the primary key, it's the second and third forms. The first deals with variable length records.

 

[CobaltWolf]

Wow. So, I was stepping into a mine-field unbeknownst :-] It's a good thing I had the forethought to tell my friend I'd only *try* to do this, that I couldn't guarantee any results. My forte is graphic design, and I've only "used" databases *indirectly* (i.e. when installing online PHP apps like CMS, galleries, blogs, forums, etc., which always require connection to a MySQL db), and I've never —*ever*— touched Access.

Thanks for the input fneily, I appreciate it a lot. What I read made sense to me, especially that bit about the junction table being a hybrid containing all those common fields. [Darn it, not even ONE of the references I read on the web mentioned such a vital piece of the puzzle... maybe because the authors thought it was already implied in their articles, something too subtle for a n00b like me to pick up.]

Still, it is a LOT of information and I'll have to sit down and go through it carefully while I digest it and try to apply it in the program (and maybe come up with additional questions). Don't know how long it will take me, but I'll post an update later whether I'm successful or not.

BTW, You were right about the extra assumptions you listed above, make lots of sense. My friend probably forgot to include those extra variables.

Ed

 

[lespaul]

try reading The Fundamentals of Relational database Design

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[CobaltWolf]

Thanks for the link Leslie, it looks like just what I need to read right now. I'll do it tonight.

And if anyone knows of a good book about it, feel free to mention it too. I prefer reading a book than Googling aimlessly for info that might or might not be there, and prefer it better than bothering people on forums like this . However, there are simply so MANY books published that it's a pain to find the right one for me (especially one that includes practical and *visual* examples on how it's done). I think I'll go to Borders tomorrow night and see what they have...

Ed

 

[fneily]

I have "Access 2000 Bible", 1500 pages, and not one word of how to Normalize. ElementK are course books for college and they extracted Normalization from their books when version 2003 came out. Good luck on your search.
My suggestion before you type in any data, you'll normalize your tables by using paper and pencil. Am I kidding?? No. It'll be easier to see and goes quicker. Each page will be a table. Then, after days doing that, turn on the computer.
Some notes on the lespaul reference:
He states that "but most developers favor numeric primary keys." True. But I suggest you create your own. Having a primary key called CustomerID with value 2 is more confusing than a value of NJH001. Someone maintaining the database or someone new in your group or even you months later can figure out NJH001. Avoid "counter columns". See fig. 2. Imagine seeing that for the first time. Who is CustomerID 1?
Normalizing - you have to AT LEAST do the first three steps IN ORDER. You can't skip any or go out of order.
Expanding on Frist normal form - make sure all fields are broken down to a single item. You shouldn't have a field Name that has John Smith in it. You should have two fields, firstname and lastname. What about phone numbers? Years ago you didn't have to tell anyone your area code. So do you have one field with 410-555-5555 or two fields, areacode and phonenumber? Only you can answer that.
Also, beware of duplicate column headings. In Fig. 7 you'll see Item1, Item2, Item3. Well, drop the number - Item, Item, Item. You have duplicate names. So you would not have a table that has Day1, Day2, Day3, etc. This, by the way, in the theory, creates VARIABLE length records which are not allowed in relational databases. All tables must be rectangular in shape without jagged right edges.
Usually, after you come out of the first nurmal form, you'll have some kind of table that'll have a multiple-field primary key. Not always, but be prepared for it. Then you go to the second form.
The second normal form describes relating fields to a multiple-field primary key table.
The third normal form describes relating fields to a single-field primary key.
The higher forms are esoteric. If you get through the first three forms, your tables are in pretty good shape.
Some trivia: Codd himself said:
"At the time, Nixon was normalizing relations with China. I figured that if he could normalize relations, then so could I".

"A relation is in second normal form if the relation depends on the key, the whole key, and nothing but the key, so help me Codd".

 

[CobaltWolf]

I have "Access 2000 Bible", 1500 pages, and not one word of how to Normalize. ElementK are course books for college and they extracted Normalization from their books when version 2003 came out.

Heh, I'll be most careful while browsing for books. I don't want to end with a $49.95 cellulose brick

After reading link that Leslie suggested, it's mind-boggling why texts leave out discussion of such useful rules. I cold use that as a litmus test while deciding on a book.

Oh, and thanks for the Codd jokes, hehe

Avoid "counter columns".

I agree seeing a "customer ID 1, or a "member ID 4" would raise my eyebrows, but what alternative is there for me? How to get Access to both generate, say, NJJ001 instead and make sure the entry will always be unique? Since at first glance I don't see how to do that in Access except by hand (which would obviously be terribly unwise), I imagine that it would entail writing some sort of SQL script then, no? I should Google that...

Thanks for your insights on Paul Litwin's article, fneily. I'll try to apply what I've managed to collect here and elsewhere and see if I can make the db work. I'll post again once I get something done (or to reply to any post).

E.

 

[lespaul]

Another perspective...a Primary Key is a way to identify a specific record in a table. Having an autoincrementing number is a valid way of accomplishing that.

As a developer I wouldn't "raise my eyebrows" if I saw a 1 in customerid field it's an identifier, if I need to know who customer #1 is then I can look that up, but in a properly designed user interface the user has NO IDEA that 1 is the key for everything having to do with 'Joe Blow's Body Shop'...all the user has to know is they are looking for information on Joe Blow enter THAT information and then the program finds out that Joe Blow is #1 and it is all transparent to the user.

So, from the other perspective I LIKE having an autoincrementing number as the key field and have it in almost every table i create.

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[fneily]

CobaltWolf - you use any method(rules) you want to create primary keys as long as they're unigue. My was just an example.
Ahhh - a discussion. If it's my own database, then anything goes, including autonumbers. But I've found that doing contracting work, there are very few people proficient in Access. I worked at Social Security Admin. in Woodlawn, MD. 27,000 people - two Access programmers. I worked down the street at HCFA (now CMS)(Medicaid and Medicare) 8,000 employees - 1 person who studied Access. I created an Access database for the State of MD. 275,000 employees, 1 person attempting an Access database. In my world, someone who has to maintain the database sees a table with customerID of 2 gets lost and confused. NJH001, they can guess it belongs to someone with initials NJH. Most of my friends - judges, lawyers, doctors, cops, blue collar workers - can understand NJH. Why be elitist? Make it simple. And I'm talking for the people who have to maintain the database, not use it. 99.8% of the population isn't as sophisticated as lespaul.