Bad design to have multiple many to many relationships?

[jk42]

Hi All,

I'm brand new to Access and this forum, and have been slated to build a database on my company's experience. I'm trying to figure out the correct relationships and table design and I'm having some issues.

The way I've broken it down, I have about 8 tables, and 6 of those 8 end up with many to many relationships with the ProjectInformation table. (Employeetbl, ManagerTbl, each employee works on many projects, each project has many employees, etc.) Is this poor design?

I originally had 3 of those now many to many relationship tables as fields in the project information table, then decided that they should each have their own table, as each of those 3 can include multiple pieces of information in each one (Example: in the work performed section, there can be several types of work selected). 4
I'm sorry if i've worded this horribly, I"m so new to access and am still learning the terminology. this is what I feel like right now --->:banghead:

ANY help would be so so so so so so appreciated

 

[RainLover]

A pic of your relationships would help.

Are you sure they are Many to Many or One to Many.

 

[gemma-the-husky]

a data design is what it is

say you have orders, and products. in most cases this is many to many. orders contain many products, and products can be on more than one order. many to many (rarely you may have a situation where an order has 1 product only - in which case this is one to many without further analysis)

so you split the many to many into 2 1-to-many relations and you end up with

Orders
Order Lines
Products

so 1 order has many order lines
and 1 product can be on multiple order lines

------

so ultimately - you need to construct your table design to suit your requirements. if you find that this design has many to many relationships, these must be decomposed into 1 to many relationships. The more used to get to doing data design, the more you just recognise the many to many situations, and split them into 1 to many appropriately without a lot of thought, to be honest.

 

[RainLover]

JK42

The Pick of your relationships will show if you have One - Many or Many - Many.

So at the moment it is not an issue.

 

[jk42]

I haven't entered anything in Access yet, it's all pen and paper. I am on my fourth try on this database, so I didn't want to jump the gun this time.

My Many to Many relationships:
-Projects can have multiple Employees, and employees can have multiple projects
-Projects can have multiple "themes" and "themes" can be used for multiple projects (or is this incorrect thinking?)
-Projects have multiple design parameters, and each design parameter will be repeated for multiple projects (again, is this incorrect thinking?)
-one Project can have several "work performed topics", and each "work performed topic" will be repeated on multiple projects (again, incorrect thinking?)
-one project can have several "indications", and each indication will be repeated on multiple projects (again, is this incorrect thinking?)

One to Many relationships
I also wasn't sure if the Client Company name (one to many relationship with projects) should be in a separate table from the client point of contact (one to many relationship with projects) for that specific project?
-Each project has one project manager, each project manager can have many projects

It's freaking me out that all of the relationships go back to the Project_Tbl... or is that how it should be, since the project history is really what I"m trying to track with this database?

 

[jk42]

a data design is what it is

say you have orders, and products. in most cases this is many to many. orders contain many products, and products can be on more than one order. many to many (rarely you may have a situation where an order has 1 product only - in which case this is one to many without further analysis)

so you split the many to many into 2 1-to-many relations and you end up with

Orders
Order Lines
Products

so 1 order has many order lines
and 1 product can be on multiple order lines

------

so ultimately - you need to construct your table design to suit your requirements. if you find that this design has many to many relationships, these must be decomposed into 1 to many relationships. The more used to get to doing data design, the more you just recognise the many to many situations, and split them into 1 to many appropriately without a lot of thought, to be honest.

Thanks! I have ultimately split them into the one to many with linking/junction tables. But I am relieved to hear that this will eventually come without much thought. Where I am right now, there is way too much thought going on!!

 

[RainLover]

-Projects can have multiple Employees, and employees can have multiple projects

This is a One to Many.

All you have done is reversed the statement.

Employee Project

A A
A B
A C
B A
B B
B C

Here you have Employee A relate to Projects A,B & C
Also you have Employee B relate to Projects A,B & C

Project A has Employee A & B
Project B has Employee A & B etc.

If it were Many to Many you would have

Employee related to Project and Location XYZ (Or something like Location)

I would still like to see a Pic of your Relationships so I can be assured that I am Right or Wrong.

If you go to the Link in my Signature you will see a sample Database called Many to Many. You can download it and have a play.

 

[jk42]

I was going to upload a picture, but I don't see how to do that?

 

[RainLover]

Read the FAQs

 

[jdraw]

Further to other suggestions, I'd recommend you read/work through this tutorial. It will lead you to normalized tables and relationships.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

Good luck.

 

[jk42]

Jdraw, believe it or not I have read all of the tutorials on that site!! I have read so many articles, tutorials, and book chapters. But as this is my first ever foray into access, I'm still very foggy on certain things.

 

[jk42]

Read the FAQs

I did, but I'm having trouble zipping the file. So, we'll see if I get it figured out! I'd love to have a second opinion. I keep talking to people who don't have any access experience, and that's probably not going to help me!

 

[jk42]

At last! Please tell me if this makes sense.

Thanks!

 

[RainLover]

I would name my Tables tblTxArea not the other way around

It matters little now but when you need to see the Tables grouped together it helps then.

Same as for Querys which should be qryDemo, frmEmployees and rptManagersResults.

Not many people do this but I name my Primary Keys InductionPK and the Foreign Key would be InductionFK. This is totally optional.

Things like Field1 are vey bad. Think about coming back to this Database (DB) in 6 months time. Will you know what Field1 is for. And to make things worse you have the same in two fields in different tables.

Some of your tables have only one field. I am sure you have more in mind.

You have 5 tables related to the one table namely Project_Tbl. I do not fully understand this.

Hope this helps a little. I have other things to do just now but I sould be able to help later.

EDIT
Spelling Mistakes

 

[jk42]

Thanks for those suggestions. I will definitely take that into consideration when I actually implement the database. I can see how that would be helpful. Yes, I have MANY fields that will go into this, I just wanted to get them in there so I can have a picture.

That was one of the things I was worried about, how they all center around Projects_tbl. Basically all of these tables that I've made in Access, are all part of ONE sheet in Excel. I am trying to make that Excel sheet a database. After reading articles, books, etc. I was under the impression that I needed to split everything up, and these are the tables I came up with.

Really, what I want to do be able to do is track which projects did we do under a certain TX and Indication, or see which projects belong to a certain client. Am I going about this all wrong?

 

[Beetle]

Think about your business rules with regards to a project and work through it logically. Ask yourself questions from an outsiders point of view. Then answer those questions. At this point you'll realize you're talking to yourself and wonder for a moment of you've gone slightly mad. It's OK. Welcome to the club.

Here are some things that I would ask, based on what you've posted so far.

In cases where you have more than one employee assigned to a project, could they be working on the same aspects of the project, or would each employee be assigned to different aspects (by aspects I mean Themes, Design Parameters, Work Topics and Indications)?

Or could it be some combination of both? In other words, could you have two employees working on the same set of aspects and a third working on a different set?

Regarding these aspects, are they interdependent? In other words, if you start a new Theme on a Project, does it then follow that there will also be a new Design Parameter, Work Topic and Indication to go along with that Theme, or are they completely independent of one another?

 

[jk42]

Thanks! My husband already thinks I"m crazy because I keep trying to logic it out by talking to him, and he has ZERO access experience!

Literally every single one of the details/tables that I posted is related to a project. Maybe I"m trying to make this too complicated?

Each project will have it's own unique of the following:
Company project code
client project code
Project Award Date
Project Name
Project Number (could be letters)
various other project details such as locatoin, how much that project is worth, etc.

Project Design
Tx
Indication

These three above in bold, I still am not sure if they even need their own table. One answer to those questions can be used on any of the projects, which led me to believe that they need their own tables. They can also have multiple answers for those three categories. I had a pretty nice thing going on a previous design...except it has lookups in the table and it ended up causing problems. So now I'm super confused as to what I"m doing. I'm fighting every Excel instinct I ahve to try and understand this properly.

Other than those listed above these are the ones that I could actually understand why they should be in a different table. There is one client contact per project, and each contact has many projects. etc. etc.
client contact name
client contact email
It will have one project manager
but multiple employees, all doing the same thing-but maybe at different locations

Does this jibberish make any sense? My brain is fried. I have literally tried this 5 times and keep getting stuck.