Many to Many cOnFuSiOn

T

the other john

The trouble currently with 3 tables. I'm excluding non-relevant
fields...

tbl_users
PK_user_ID

tbl_developers
PK_developer_ID
FK_developer_user_ID
FK_developer_project_ID

tbl_projects
PK_project_ID


This is an application that manages users with 3 levels of access. All
users are in the users table and their access level is identified
there. The three levels are 1 (admin), 2 (developer), and 3 (client).
Currently my problem is with developers. I originally created the
developers table as a "bridge" table. What's backfiring that
intention is that there could be more than one developer for each
project, creating a many to many. How can I solve this?

Thanks!!
 
B

Bob Barrows [MVP]

the said:
The trouble currently with 3 tables. I'm excluding non-relevant
fields...

tbl_users
PK_user_ID

tbl_developers
PK_developer_ID
FK_developer_user_ID
FK_developer_project_ID

tbl_projects
PK_project_ID


This is an application that manages users with 3 levels of access.
All users are in the users table and their access level is identified
there. The three levels are 1 (admin), 2 (developer), and 3 (client).
Currently my problem is with developers. I originally created the
developers table as a "bridge" table. What's backfiring that
intention is that there could be more than one developer for each
project, creating a many to many.

Well, that is the reason for creating a bridge table: to resolve a
many-to-many relationship. Why do you say this is "backfiring"?
How can I solve this?
What do you need to solve?
 
T

the other john

The there can be many developers for many projects, I don't know how to
solve this. As I said, I'm confused. Am I making this harder? Am I
not seeing something
 
B

Bob Barrows [MVP]

the said:
The there can be many developers for many projects, I don't know how
to solve this. As I said, I'm confused. Am I making this harder?
Am I not seeing something

:)
I guess so. I'm certainly not seeing something ...

What is the problem? Give us something specific to answer. What immediate
task are you having an issue with?

When you say you don't know how to "solve this", what do you mean by "this"?
I mean: you've got your database correctly designed to model the
many-to-many relationship. So there must be some other task that's confusing
you. What task is that?

Bob Barrows
 
T

the other john

I'm trying to eliminate the many to many relationship. if more than
one developer can be assigned to more than one project that makes the
relationship between the project and developer tables many to many,
true?
 
B

Bob Barrows [MVP]

the said:
I'm trying to eliminate the many to many relationship. if more than
one developer can be assigned to more than one project that makes the
relationship between the project and developer tables many to many,
true?
True.
Isn't that the correct relationship? Is that the problem? You want to have
only a single developer assigned to a project? If so, change the design to:

tbl_users
PK_user_ID

tbl_developers
PK_developer_ID
FK_developer_user_ID

tbl_projects
PK_project_ID
FK_developer_ID

In this design, tbl_developers is no longer a "bridging" table: it is now a
"subclass" table. And there is now a 1-to-many relationship between
developers and projects
 
T

the other john

actually I need there to "potentially" be more than one developer
assigned to a project. that's what's confusing me. If the developer
was unique to the project then this would be easier but with the
"potential" for more than one I don't know how to proceed.
 
B

Bob Barrows [MVP]

the said:
actually I need there to "potentially" be more than one developer
assigned to a project.

So you need a many-to-many relationship. Your original design handles
this.What's the problem?
that's what's confusing me.

I'm sorry, but I just don't understand what's confusing you.
If the developer
was unique to the project then this

What do you mean by "this"?
would be easier but with the
"potential" for more than one I don't know how to proceed.

You "don't know how to proceed " with what? What is the next task you need
to perform that your confusion is preventing you from performing? Be
specific.

Bob Barrows
 
T

the other john

I must not understand something here because I'm being as specific as I
can. I'm trying to "get rid" of the many-to-many relationship and
create, somehow, a one to many relationship (while still complying with
the "more than one developer" need) as M2M is not RDBM compliant, or in
short, not good.

actually I need there to "potentially" be more than one developer
assigned to a project.


So you need a many-to-many relationship. Your original design handles
this.What's the problem?

that's what's confusing me.


I'm sorry, but I just don't understand what's confusing you.

If the developer
was unique to the project then this


What do you mean by "this"?

would be easier but with the
"potential" for more than one I don't know how to proceed.


You "don't know how to proceed " with what? What is the next task you
need
to perform that your confusion is preventing you from performing? Be
specific.

Bob Barrows
 
B

Bob Barrows [MVP]

the said:
I must not understand something here because I'm being as specific as
I can. I'm trying to "get rid" of the many-to-many relationship and
create, somehow, a one to many relationship (while still complying
with the "more than one developer" need)
:)
You mean like getting a woman "almost pregnant"?
Well that is just not possible. You either have a 1-to-many relationship or
a many-to-many relationship. There is nothing in-between. If a project can
have more than one developer assigned to it, and a developer can be assigned
to multiple projects, then, by definition, you have a many-to-many
relationship. What is the problem?
as M2M is not RDBM compliant,

Excuse me????
Where in the world did you get this idea? A many-to-many relationship
resolved by a "bridge" or "link" table is certainly "RDBM compliant".
Show me the normalization rule that is broken by a M2M relationship ...
or in short, not good.

And what is "not good" about it? Using this model, you can easily get a list
of developers assigned to a specific project, right? And it's child's play
to get a list of projects assigned to a specific developer, right? What is
the problem?
 
T

the other john

LOL, that's what I like about you Bob...dramatic emphasis!

ok, so M2M is alright in this case then. All the beginner books keep
telling me that's is a no-no so I try to avoid them is all.

So all is good then, we'll leave it as is.
 
C

Chris Hohmann

the other john said:
LOL, that's what I like about you Bob...dramatic emphasis!

ok, so M2M is alright in this case then. All the beginner books keep
telling me that's is a no-no so I try to avoid them is all.

So all is good then, we'll leave it as is.

Many-to-many relationships are a "no-no". That's why you use a bridge table
to convert the many-to-many relationship into two(2) one-to-many
relationships. It may be more correct to state that "direct" many-to-many
relationships are a "no-no".
 
B

Bob Barrows [MVP]

Chris said:
Many-to-many relationships are a "no-no". That's why you use a bridge
table to convert the many-to-many relationship into two(2) one-to-many
relationships. It may be more correct to state that "direct"
many-to-many relationships are a "no-no".

Hmm, maybe that's what he's talking about.
 
A

Anthony Jones

It may be more correct to state that "direct" many-to-many
relationships are a "no-no".

I don't know of any DB Engines where this is even possible.

It's a bit like saying, seating 4 elephants in a Mini is a "no-no".

Anthony.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top