Many-many: change to 1-many?

[WrHn85]

Hi, hope someone knows the answer to this one...

We started a new business and built a db for expecting our work to happen a certain way, but now find it's very different and need to change the structure before it's too late to fix. Can someone please take a quick look at this idea & tell me if it's likely to cause major problems down the road?

People send in applications for specific groups/clubs. We thought they would arrive separately, so we set it up to enter each application and then add them to the Group listing one by one. TblApplication is a many-many with tblGroup, with junction tblGroupAppl. (People can be in more than one group.)

As we go along, however, we are finding the opposite: all the individual applications are being sent together by the club and so now we could actually turn this into a simple 1-many with tblGroup as parent and tblApplication as child. It seems like it would be much easier with queries and such if that extra step could be eliminated.

If I go through and put the GroupID on each record of tblApplication, can you see anything I might be inadvertently wrecking with this approach?

Any ideas/suggestions would be most appreciated!!

Thanks, this forum is amazing.

 

[Fuga]

I´m not sure I understand, but if you do it this way, won´t there be a problem when one applicant belongs to several groups?

Fuga.

 

[WrHn85]

Good point - we thought we would just re-use the name in different groups, but it may mean we need to set up a new application for that person each time. I'll test these & check what happens.

thanks very much

 

[Fuga]

If you re-use the name then you will have redundant data in your table, but that might not be a problem.

But if, in addition to name, you also have information about the applicants adress etc, you will have trouble if the person moves. Provided He/she belongs to many groups.

As you may have guessed, I like your first approach better.

Fuga.

 

[WrHn85]

Fuga, you're right. The relationship never stops being many-many in this situation. Would be one thing for storing historical info on a person but would be awful for keeping track of their membership info in general.

Thanks very much for another perspective and for the help!

 

[Fuga]

Glad to help.

For more info on the subject, do a search for "normalization".

Fuga