Many to Many Relationship? Should be done?

[bjallen2880]

Greetings all, I've been ripping out my hair trying to figure out the best way to house this data in Access yet still be able to display it afterwards in a sensible fashion.

Here's the scenario: Making a database to show the IT Guy, the Locations he Supports (which are sub-divided into markets, and that's how the IT Guy's relation to the locations is to be shown). There are a group of IT Guys that report to the same supervisor. For each market, there could be one or many IT Guys supporting it. On the flip side, an IT Guy could also support more than one Market.

Example: Joe supports Pennsylvania. Jane supports Ohio and West Virginia. Bob and Bill both support New York. Jack and Jill support Maryland and Delaware.

Currently I have a Locations Table that has all the locations in there, which have the Market sub-division. I have the ITGuy table with name and supervisor (plus personal info). I have a Market table with the market name and the supervisor that relates to it and all his ITGuys that support it. And finally I have a supervisor table.

The problem I'm running into is when I finally go to show information, I want to be able to say "Market Maryland is supported by Jack and Jill, and these are the locations"

Is that possibile with the layout I have?

 

[bjallen2880]

Here's a screenshot of my current layout of relationships.

I appreciate any help! Thanks!

 

[bjallen2880]

I did some digging on google, and came across making a "middle man" to help with the many to many data. Attached is a new screen shot of the new relationships. But now I can't make one-to-many in between tbl_ITA and tbl_RetailStore. It find the relationship as indeterminate.

The way the tbl_MarketITA is laid out is (both colums are primary keys)
Philadelphia Jack Sprat
Philadelphia Jane Doe
Pittsburgh Jack Sprat
New Jersey Tom Thumb

What are your thoughts on this layout? Am I all wrong?

 

[grnzbra]

The join table looks good, and you would also include in that any information that applied to that particular territory/techie combination such as HomeTerritory yes/no, TravelTimePaid yes/no (or amount with 0 for no and any amout for yes).

However, I am wondering about having a table for techies and a table for supervisors and a table for management. Since they are all persons, with similar attributes, they should be in the same table. The techie would have a supervisor field which, in a query would link back to a second occurrance of the personel table on the supervisors id. The supervisors and managers would be similarly linked.

 

[WindSailor]

Here are a few links on normalization...

http://www.access-programmers.co.uk/forums/showthread.php?t=100211

http://www.utteraccess.com/forums/s...=85896&Zd=l&Zn=&Zt=24&Zs=&Zy=#Post1203187&Zp=

Each link has multiple examples and tutorials...

Normalizing a database I believe should be roughly 50% of your applications creation time. It is extremely important to do it right, highly personal (don't really know of two people who do it the same way except for generics) and sometimes -more often than not- simply gets over-complicated. Yes a real pain in the butt.

I'll let other people critique your example, the suggestion that I have is to seriously look at the examples in the links above. And I try to stay away from 1 to 1 relationships if at all possible (which it should be). And... keep it simple. Simplify, Simplify, Simplify.

 

[stopher]

I did some digging on google, and came across making a "middle man" to help with the many to many data. Attached is a new screen shot of the new relationships. But now I can't make one-to-many in between tbl_ITA and tbl_RetailStore. It find the relationship as indeterminate.

The way the tbl_MarketITA is laid out is (both colums are primary keys)
Philadelphia Jack Sprat
Philadelphia Jane Doe
Pittsburgh Jack Sprat
New Jersey Tom Thumb

What are your thoughts on this layout? Am I all wrong?

OK, here goes...
Your idea for tbl_MarketITA is correct for dealing with the many-to-many. The link to tbl_ITA is in principle correct. However, you do not appear to have assigned a PK (primary key) to this table. This is a mandatory requirement of all tables (see the normalisation rules). So delete the relationships to this table, assign a PK, then recreate the relationships. This should solve your problem about the one-to-many symbols not being displayed. On the same subject, what is fld_PNumber ? If this is a unique number for your IT guy then this should be the PK. If fld_ITA is really a name then I would strongly avoid using this as a PK. Once you have decided your PK then you should use the PK as the link for referencing the IT guy i.e. reference using the ITguys number not his name.

On the market side of the tbl_MarketITA table I'm slightly confused as to what a market is and stores and offices are related. I think you need a Market table so that you can show which retailers belong to which market. You can't simply join RetailStore to MarketITA because you end up creating another many to many (same with offices). Maybe I just don't get what you are saying about locations and markets. RetailStore needs a PK anyway.

As for the point made by grnzbra, I'd agree with grnzbra's solution. You can implement this in access by first creating a single People table which includes a field called say fld_ReportsTo as well as a PK (not a name !!). Then add the table to the relationship view as normal. Then add it again - you will see a second view of the table with a slightly different name. This is an alias of the first table. You can then drag fld_ReportsTo from one table onto the PK of the other table. This will create a one-to-many relationship. The number of hierarchys becomes irrelevant as the relationship knows who reports to who. You may want to add a field in the People table that identifies their position (grunt, supervisor, manager). This will allow you to report at each level as well as apply contraints such as "all grunts must have a manager", "All supervisors and grunts must be responsible for a market" or whatever.

hth
Chris

 

[bjallen2880]

Before I attempt to make changes based on all of your awesome suggestions, I just wanted to say thanks for your help! These suggestions will really get this DB going!

To answer some questions though -
a "Market" in this database is a division of the retail stores into a group, say Central PA, Buffalo, Philadelphia North, Philadelphia South. The reason for the division is because each division has a separate manager for all of those stores (but not related to the IT Guys, supervisors, or managers). But our department does use this division to help us with assigning an IT Guy (or more) to these markets.

The PNumber field is our individual employee ID number. This will become the Primary Key. It wasn't set as the PK before because there were blank sections for open ITGuy positions. We'll work around that a different way.

Again, thanks for the help, and once these changes are made, I'll show you all how it looks.

 

[bjallen2880]

Thanks again for everyone's suggestions. I have implemented them and now this is how it looks.

 

[bjallen2880]

I just thought of something. This way will not work (or maybe it will, let me know).

I need to be able to sort everything by the Retail Store's Market (each Market is comprised of around 10-15 stores). So the end resulting report can be something like - Market Philadelphia North is supported by Jane Doe and Jack Frick (referenced by tbl_Employees), they have 123 stores (referenced from tbl_RetailStore), 4 offices (referenced from tbl_Offices), and support a total of 432 users (add total of fld_Users from both tbl_Office and tbl_RetailStore).

In my mind, the way to accomplish this would be to have the Many-to-Many relationship thru 4 tables. The new Markets table can include data exclusive to that Market, such as the Regional Manager and his/her Administrative Assistant.

tbl_Employees​

linked via fld_PNumber​

tbl_MarketITA​

linked via fld_Market​

tbl_Markets​

linked via fld_fld_Market​

tbl_RetailStore​

 

[bjallen2880]

I just thought of something. This way will not work (or maybe it will, let me know).

I need to be able to sort everything by the Retail Store's Market (each Market is comprised of around 10-15 stores). So the end resulting report can be something like - Market Philadelphia North is supported by Jane Doe and Jack Frick (referenced by tbl_Employees), they have 123 stores (referenced from tbl_RetailStore), 4 offices (referenced from tbl_Offices), and support a total of 432 users (add total of fld_Users from both tbl_Office and tbl_RetailStore).

In my mind, the way to accomplish this would be to have the Many-to-Many relationship thru 4 tables. The new Markets table can include data exclusive to that Market, such as the Regional Manager and his/her Administrative Assistant.

tbl_Employees​

linked via fld_PNumber​

tbl_MarketITA​

linked via fld_Market​

tbl_Markets​

linked via fld_fld_Market​

tbl_RetailStore​

I just realized that this is totally dumb and redundant. Here's what it looks like.

 

[stopher]

The bit you've circled looks great. I see you have managed to get the one-to-many relationship between Market and Retailstore so you need to delete it and redo it. But apart from that you can answer the kind of questions you are asking with this design.

I see you have sorted offices. This looks fine too. I assume offices are in no way directly related to markets of stores other than by association through employee. In which case you're fine.

You mentioned earlier about not always having an employee in position. This could be a pain with this design because each time you change employee you will have to do lots of reassignments. One way to deal with this is to create a table called Position which sits where you currently have employee. Position would have a couple of attributes say PositionName and EmployeeID and a PK - PositionID. PositionName might have values like "Engineer1", "Enginner2" etc. The point is you can then create all your static positions and allocate them to markets etc. But the EmployeeID can be empty and completed only when you have an employee filling the position. Changing the employee for that position becomes easy.

hth
Chris

 

[bjallen2880]

I tried deleting and re-creating the relationship between tbl_Market and tbl_RetailStore for the fld_Market but it would not create any form of relationship. It kept coming up indeterminate (perhaps because of the data in there?).

I may try again with it but create a dummy field (minus data too) in tbl_RetailStore to see if the relationship creates.

Stopher - yes, offices and retail stores have no direct relation other than the employee that supports them. That's why they have separate tables (and different fields inside as well). They kind of fall into a market category as well, so I may be able to link them to the tbl_Market table.

tbl_DistributionCenters are by themselves and only have one employee per center, so they are okay as they are.

Thanks for your help!

 

[bjallen2880]

I made some more changes. Of course now the relationships work! It works a lot better when there is little or no data in there!

Attached is a new screenshot of the relationships.