Help with creating relationship - many to many

[candacelarue]

Hi,
I hope you can help me. I am new to this forum, and although I am not new to using Microsoft Access, I am not an expert either. Furthermore, it has been a couple of years since I was last using Access regularly and suddenly I am confronted with an important project.
I am designing a database to keep track of proposals. There are about 225 of these proposals and they each address a different number of schools. I need to design a database that will allow me to connect the applicant from the proposal to the school they are addressing. Some applicants may have one school, some may have as many as 35. Some schools may be addressed by more than one proposal and I need to be able to sort for that. There is also specific data I need to maintain for each school - about 5 fields. The data will change according to the applicant. Example:
Application A is serving 6 schools. They are providing services to 100 children at each school, in grades 3-5.
Application B is serving 5 schools, 2 of whom overlap with the schools in Application A. However, the students they are serving in one of those 2 schools come from grades 1-4 and the other school has children from grades 3-5 but only is serving 25 children in each of their schools.
Can anyone give me some advice on the simplest way to set up these tables and relationships? I started by putting everything in one table, but in those cases where the applicant is proposing to serve 30 schools it is going to get difficult. Additionally, if I have fields for school 1, school 2, school 3, etc, I am not sure how I will be able to cross check the schools if, for example the same school gets entered for three different applications, but is in the school 1 field for the first application, ends up in the school 3 field for the next one, and is at the end of the list of 20, and is therefore in field school 20 for the 3rd application.
I hope this question makes sense. Please let me know if it does not and I will attempt to clarify.
Candace

 

[Darrylles]

Hi,

The tables/relationships you need are as follows:
[tt]
Relationships:
-------------
Applicant 1 ---- M Proposal
Proposal 1 ---- M School
School 1 ---- M Grades

Tables:
------
tblApplicant
pk : Long(Auto)
(details)

tblProposal
pk : Long(Auto)
(details)
app_fk : Long(link to tblApplicant)

tblSchool
pk : Long(auto)
(details)
pro_fk : Long(link to tblProposal)

tblGrades
pk : Long(auto)
(details)
sch_fk : Long(link to tblSchool)
[/tt]

You'll get all info. you require from these relationships.

If you want detailed help - email me below.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[kupe]

thanks, darrylles - says a passing (and interested) browser - but what would you choose for the primary/foreign keys? - Sinbad

 

[JeremyNYC]

Those PKs should be autonumbers. Having significant PKs only leads to headaches.

Jeremy

PS Darrylle: It's very nice of you to offer to help offline, but best for the rest of the community if you do all of that helping here, so that others can learn as well. In any case, I want to be clear that I'm not being sarcastic about it being very nice of you to help off line. Cheers. =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[candacelarue]

Hi again, and a sincere thank you to all that are helping me. I am using Access 2002, by the way if that helps in this discussion.

I am sadly, still not getting it, and that is partly because I don't think I described my needs well enough, having not figured it out clearly enough before. If I may, I would like to try again to describe what I am trying to do, since when I sat down at the keyboard I realized there are actually four tables (I think) that I need to incorporate into this design.

The tables are:
1. Program Proposals
2. School Buildings
3. Numbers served
4. Program Sites

Program Proposals: This is the "main" table. There are 225 proposals that I need to track. The primary key in this case is a proposal number that was assigned as the proposals came in the door. Each one is, of course, different. In all other cases I used an auto assigned number for the primary key.

School Buildings: Students who are attending the programs that are in the proposals may all come from one school building or they may be from as many as 35 different school buildings. Unfortunately, there may be more than one proposal which is addressing students from the same school building.

Numbers served: I need to know how many students from each school building and what grade levels are being served in each proposal.

Program Sites: This is the one I forgot last night. The location of the program may not be at the school building the student attends during the day. I have to track what and how many sites are proposed in the proposals. There may be some overlap of program sites being proposed in more than one proposal too.

I am really unsure how to proceed. Any more details will be really appreciated. Don't hesitate to explain it at too simplistic a level - I have been away from using access to do database application design for about 4 years, and I seem to have forgotten an awful lot!

By the way, I volunteer my expertise on a gardening experts site - if any of you ever need any help in that direction let me know! <g>

Candace

 

[pelegs]

JeremyNYC said:
Those PKs should be autonumbers. Having significant PKs only leads to headaches.

Well, then, why use a PK at all? That is not what a PK is supposed to be. They are supposed to be one or more columns from the naturally occuring data. Now, I know a lot of people don't agree with this. Then, you also wouldn't agree with C.J. Date, who is one of the inventors, along with Codd, of the relational model. I've studied the book by Date, An Introduction to Database Systems and I find whenever I deviate from the words of the masters, I get into trouble. They have a sound mathematical basis for their definition of PK and it is hard to make, in my mind, a winning arguement against it. The math holds up well.

I've done it both ways but now I never make the PK an autonum, although I do use them because they are easier to use in Joins than a long, concatenated key. Sure, this may be cheating a bit, and I now have an extra column, but I get it for free, it is guaranteed to correspond 1:1 with my PK, and when adding rows, having a proper PK automatically insures that no duplicates find their way into the data. An autonum doesn't insure at all against duplicate data being entered. People are lazy. They will look quickly for the row, if they don't find it, then they'll just add it... again. A real PK has a fighting chance of blocking this duplicate add. Been there, done that, pulled my hair out trying to clean out the duplicates and adjust all the foreign keys in the other tables. That was not fun, but it needed to be done because data was effectively being lost.

Also, the user is never aware that the autonum row even exits. It is only used behind the scenes to make coding cleaner and easier. But they are still not declared as my PK.

It can sometimes be difficult to choose the PK. Name and address lists are my favorite difficult example. Too many people are named John Smith. So, what I find I have to do is include the first address line or the zipcode. I've considered using the phone number, but the phone is not always available when you need to add the row. If the address changes, referential integrity can take care of that, but it becomes a non-issue if the referential integrity declaration uses my autonum cheat-columns.

I've never had any problems using a proper PK. I wonder what problems you've had? It would be interesting to me to know. Maybe you've been in some sticky situations that I haven't the pleasure of experiencing yet. I certainly understand that there are odd situations which require violating first principles, but they are, in my experience, rather rare. I know that my use of an autonum can be considered such a violation, but in 9 years of doing Access, I haven't found this violation to be harmful.
Peleg
PelegNOSPAM@PStrauss.net

 

[Darrylles]

Hi,

Kupe, the answer is before your question.

Pelegs, sorry - but J is right of course.

You want to pin designers down to Date & Codd, but yet admit that even you vary from the 'masters' methodologies, by using autonum when you see fit.

Just to prove a point, when did you ever go to 5th normal form (even when you could)? I'll bet you couldn't be bothered - because the return wasn't worth the effort.

Same with us autonum users - look at that and agree.
Technology doesn't require absolute efficency as declared by Codd - it becomes inefficient in todays technology.

Candace - I'm looking at your email (will post everything here).

Regards,

Darrylle

&quot;Never argue with an idiot, he'll bring you down to his level - then beat you with experience.&quot; darrylles@totalise.co.uk

 

[JeremyNYC]

Peleg,

You've encountered situations in which you can't get the phone, but not situations in which you can't get the address? And what if two John Smiths live together? Same addy, same phone, same zip. Your system doesn't work for these situations. Natural keys are problematic. They always have been and they always will be. And there is no need for them. If you get your autonumber column for free, then so do I. So why not use it properly? Data validation should take care of duplicates in a table of people, not multi-part PKs, which tend toward failure. In addition, validation can be a lot more robust than disallowing duplicates. If you do your own validation, it's easy enough to use a Soundex function to look for similar entries and flag these for the user.

In addition, it seems absolutely silly to me to be making joins from a surrogate key (the autonumber) that has not been made the primary key. There's very little logic there.

But mostly I disagree with you because of the real-world implications. I do not think your system is as robust as one with an autonum PK and intelligent data validation. I would never sell a product to a client if it was going to fall down the first time two people with the same name lived together.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[pelegs]

Jeremy,

You've encountered situations in which you can't get the phone, but not situations in which you can't get the address?

The situation was that a name was obtained as a referral. We actually had to handle the situation where we only had a name and not much else. My contention was that we shouldn't even be entering these people in the first place, but management always knows better.

And what if two John Smiths live together? Same addy, same phone, same zip. Your system doesn't work for these situations.

I whole-heartly agree. I've even pondered that possibility, lost some sleep over it, and ended up with a headache over it. In this case, natural keys are problematic. And I always cite name lists as the one nasty situtation where natural keys just don't work very well. But, that is the only situation I've ever run across where a natural key can't be chosen very well, and most of the time, it is rather easy to pick them. I know, you've got a slew of other nasty situations that just don't work. What would they be? The challenge of at least trying to pick natural keys I think I'd find enlightning. I could always use the practice.

Natural keys are problematic. They always have been and they always will be. And there is no need for them. If you get your autonumber column for free, then so do I. So why not use it properly?

They can be problematic, but in my experience, not very often. The need for natural keys is a result of the fact that a table is the same thing as a set, and a set cannot have duplicate elements, by definition. As far as I know, everything that is true of a set is true of a table. Only natural keys can insure that no rows are duplicated. As an autonum has nothing to do, really, with the actual data (or attributes, as Codd refers to them), that is, no other columns depend on it, it can't function as a natural key. I only use it as sort of an alias for the natural key.

I simply can't see how using an autonum as a key is a proper use of the autonum. I can't see how it satisfies the Relational Model, how is satifies the definition of a natural key. The Model doesn't allow other mechanisms to insure the uniqueness of rows -- it is supposed to be an inherent propety of the naturally-occuring data, enforceable by the database engine itself, and not dependent on some outside agent (user code). How the database engine enforces this uniquesness constraint is an implementation issue that the Model doesn't address (on purpose -- it pointedly ignores implementation issues), but the Model simply demands that it must be done as a criteria for conformance to the Model. Autonums are handy, that I will admit, but that doesn't make them proper primary keys.

Data validation should take care of duplicates in a table of people, not multi-part PKs, which tend toward failure. In addition, validation can be a lot more robust than disallowing duplicates. If you do your own validation, it's easy enough to use a Soundex function to look for similar entries and flag these for the user.

Excellent points. If you haven't chosen a proper primary key, then you have to do all validation yourself, you can't take advantage of Access to help you out here. If, however, you let Access take care of this for you, then your data validation reduces to just making sure you've got syntactically correct data. Sometimes, you can also validate the semantics, but it is harder, nearly impossible, to tell if, say a phone number has transposed digits. The database engine knows whether you can add the row or not, and it tells for free, but only if you've chosen Model-conforming primay keys.

In addition, it seems absolutely silly to me to be making joins from a surrogate key (the autonumber) that has not been made the primary key. There's very little logic there.

Well, I've tried not using autonums. It works, it works well; in fact, it is guaranteed to work. The reason I use autonums is simply because when there is a concatenated key, Joins are just easier to write. Plus, I think they might run a bit faster with a simple long integer key. I'm not saying I am absolutely correct here -- I am saying I am absolutely lazy. But I have not found it to be harmful. And for tables where the Primary key is only one column, I don't use autonums on that table at all.

But mostly I disagree with you because of the real-world implications. I do not think your system is as robust as one with an autonum PK and intelligent data validation. I would never sell a product to a client if it was going to fall down the first time two people with the same name lived together.

Yes, the real-world does get messy and sometimes, in its onery sort of way, just refuses to conform to the mathematical model. And I think that Codd would agree with me when I say that you should always try to stay with the math model, and only deviate from it cautiously, purposefully, and with plenty of documentation when you have to. The math is solid. Conforming to it results in solid apps. That is the beauty and power of the math. Still, as a practical matter, either the real-world won't fit the model, Normalization introduces unacceptable inefficiencies, or for other unforseen reasons, you have to deviate, then I am not opposed to deviation. I just don't do it unless there isn't a better way, and note that I'm using &quot;better&quot; here in a strictly context-dependent way.

My experience is that my apps are actually more robust when I adhere closely to the Model. When I have deviated, either purposefully or by accident, I've encountered problems when I've had to make mods. I've seen the Model save my bacon too many times to toss it off without great caution.

And as another practical matter, if two John Smiths had the same address and phone, I'd expect one would be the son, and so you could differeniate them because one would be Sr, or the &quot;John Smith, II&quot;, etc. I guess this could happen, but for this one higly unlikely case, do we really have to worry about two totally unrealated John Smiths living at the same address?

In the end, what counts, what we are paid for, is for apps that work, work right, work right all the time (or at least as often as humanly possible), and are efficient enough with respect to response time and other resources. If your apps are like this, then I would have no reason to criticize them, except in this abstract sort of way. Even if I inherited the maintanence of one of your apps, I wouldn't change it to fit my style. That would not only be wasteful and senseless, that would be terribly arrogant of me.

I like to say that there are at least 12 different ways to write any program. Now, about 6 of them are so terrible, that you should be taken out at sunrise and shot for foisting such a terrible creation on mankind. Still there are about 6 ways left. And these 6 all work, work well enough, and are OK. Of the 6 good ways, about three are passible. Not bad at all, but not great either. Still, that leaves about 3 other good ways, and one would have to say that these last 3 are all really excellent implementations, very good in every way. And I always try to remember that my way, even if I think it a really good way, is still only one at least a few ways to do it that are as good, or even better than, my way. What I constantly look for is people who know how to do things better than me and take a lesson.

Anyway, I'm having a grand old time with this discussion. I glad we can keep it friendly. Next to writing programs, I like talking about them best.


Regards,
Peleg
Peleg
PelegNOSPAM@PStrauss.net

 

[JeremyNYC]

This post was too long for me to care to read it. I will say that capitalizing &quot;model&quot; makes you look a little scared of the people who write about databases.

You can make all the compound indexes you want in a table, but a primary key is a way to refer to records in that table, and it makes no sense to have seven columns acting as a foreign key in a bunch of tables floating around my databases.

The number of easy examples of data sets with no sensible single-column naturaly PK is too big for me to imagine that you're serious when you say you have a hard time coming up with any.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[gusbrunston]

[tt]
Butting in...

In the book, Microsoft Access 2000 Bible by Cary Prague and Michael Irwin, I read: &quot;In Access, you cannot use an AutoNumber data field to enforce referential integrity between tables. Therefore it is important to specify another data type--such as Text or Numeric--for the primary key.&quot;

If true, how important is it to enforce referential integrity?

[tt] Gus Brunston - Access2000[/tt] Intermediate skills.

 

[JeremyNYC]

Gus,

That quote means that you can't use an autonumber field as a _foreign key_ to enforce ri. I use autonumber keys as a primary key with ri every day. Try it. Quite easy.

Jeremy

=============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[Craig0201]

Peleg,

Some truth and some half truth in your post. Hence my additions.

&quot;And what if two John Smiths live together? Same addy, same phone, same zip. Your system doesn't work for these situations.&quot; Easy, SSN in US, NI in UK

&quot;C.J. Date, who is one of the inventors, along with Codd, of the relational model.&quot; Incorrect, Codd and a team of reasearchers at IBM created the relational model. Date is an academic.

&quot;Autonums are handy, that I will admit, but that doesn't make them proper primary keys.&quot; True but easily solved by putting a unique index on the natural PK column(s). This is a common technique and ultimately has roots in Information Theory.

&quot;My experience is that my apps are actually more robust when I adhere closely to the Model.&quot; True but they can be slow. Most commercial apps either use clustering techniques or controlled denormalisation. For example, do you hold a table of addresses or do the addresses go with the customer record? Under the relational model, the address should be held elsewhere and only the house number and postcode held. (UK example) Why? Because the postcode is a PK on most of the address and only the house number needs to be varied. But most apps don't do this because it is slow, cumbersome and mostly necessary.

Understanding the model is important but deviating safely will help the app speed no end.

Craig

 

[JeremyNYC]

Craig,

I agree with what you say, except for one part, which happened to be a response to Peleg's quote of something I said, so i'll respond to that...

I said this part:
&quot;And what if two John Smiths live together? Same addy, same phone, same zip. Your system doesn't work for these situations.&quot;

You said this:
Easy, SSN in US, NI in UK

I disagree with this system because there are many cases where SSN is not collected, and I would assume the same is true of NI. These may be unique identifiers of people, but there's no way I am going to ask someone for their SSN if I'm trying to sell them something. And there are many other situations where it's not appropriate to ask for this kind of information. And there are many situations in which I personally wouldn't give that information if a person asked for it.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[kupe]

sorry, jeremy, i don't get it. you can use autonumber as the primary key, you say. but if you do, where's the relationship with the foreign key's table?

say it's for yachts in a race and the primary table has the yachts and three or four fields, and all the other details are in the secondary table. wouldn't it make sense to have the yacht names or skippers' names as the primary and the foreign key? there is a direct relationship there.

i'm not doubting you are right, but how does the autonumber tie the basics with the peripheries please? as you will gather, relationships remain a dark art for me.

cheers and a happy christmas to you all from gloomy uk

kupe

 

[JeremyNYC]

Kupe,

The autonumber field in the tblYacht is known as a surrogate key. It's a surrogate because it stands in for a piece of real-world data like the yacht name. This is done for several reasons, including:
-Captain's name isn't a great choice because it's quite easy for a captain to move from one yacht to another.
-Yacht name isn't a great choice because, though it may happen less often than the above, Yachts are sometime re-christened.
-Typing in names of any sort means you'll end up with bad data sooner or later.

In the end, most developers use a numeric field as the PK for their tables, and then a numeric field in other tables to server as the FK.

If relationships are still a dark art to you, check out the &quot;Fundamentals&quot; article on my website. It will help a lot.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[Craig0201]

Jeremy,

True but it is an example to show that for every person there is a unique identifier without having an invented key. Personally, for most apps I would do exactly like you and use an AutoNumber as a PK and create a unique index on the name, address and DOB. Any parents giving their children exactly the same name are distinctly odd!!!

ANd as a clarifier on my previous post.....

&quot;But most apps don't do this because it is slow, cumbersome and mostly necessary.&quot; Should read

&quot;But most apps don't do this because it is slow, cumbersome and mostly UNnecessary.&quot;

Craig

 

[Darrylles]

Hi,

I - because I insist on normalization to the nth degree (but not 5th lol) have been labelled as a rdbms snob in this forum.
I do this for 'future' development efficiency (takes longer to design, admitted - but provides less duplication and is easier to maintain in the long-term).

The above arguments for using 'natural' keys are obviously from people who wish to cling to tradition.
Try using autonum in comparison to natural keys in an 'identical' small project - try it!

You can't honestly have done this.

Anyway: Candace has solved it (her own way):

Proposals
Buildings
Sites
Building details
Site details

I am using the two details tables to connect the
proposal table to the buildings and sites tables. It
looks like this:

Proposal 1 ---- M Building Details
Proposal 1 ---- M Site Details
Site Details M ---- 1 Sites
Building Details M ---- 1 Buildings

The primary key in the proposals is the proposal
number (a unique identifier that is a number). The
primary key in the other tables are autonumbers. My
forms all work with the subforms and I am glad this is
not what I do full time!

Again, thanks for your help.

LMAO (Darrylle)

Regards,

Darrylle


&quot;Never argue with an idiot, he'll bring you down to his level - then beat you with experience.&quot; darrylles@totalise.co.uk