ASP - Many to Many cOnFuSiOn

Author

Many to Many cOnFuSiOn

the other john

2006-02-15, 7:55 am

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!!

Bob Barrows [MVP]

2006-02-15, 7:55 am

the other john wrote:
> 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?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

the other john

2006-02-15, 7:55 am

The there can be many developers for many projects, I don't know how to
solve this. As I said, I'm

. Am I making this harder? Am I
not seeing something

Bob Barrows [MVP]

2006-02-15, 6:55 pm

the other john wrote:
> The there can be many developers for many projects, I don't know how
> to solve this. As I said, I'm

. 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
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

the other john

2006-02-15, 6:55 pm

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?

Bob Barrows [MVP]

2006-02-15, 6:55 pm

the other john wrote:
> 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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

the other john

2006-02-15, 6:55 pm

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.

Bob Barrows [MVP]

2006-02-15, 6:55 pm

the other john wrote:
> 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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

the other john

2006-02-15, 6:55 pm

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.

the other john wrote:
> 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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Bob Barrows [MVP]

2006-02-15, 6:55 pm

the other john wrote:
> 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?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

the other john

2006-02-15, 6:55 pm

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.

Chris Hohmann

2006-02-22, 6:55 pm

"the other john" <kinane3@yahoo.com> wrote in message
news:1140022773.035627.310420@z14g2000cwz.googlegroups.com...
> 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".

Bob Barrows [MVP]

2006-02-22, 6:55 pm

Chris Hohmann wrote:
> "the other john" <kinane3@yahoo.com> wrote in message
> news:1140022773.035627.310420@z14g2000cwz.googlegroups.com...
>
> 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.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Anthony Jones

2006-02-23, 6:55 pm

>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.

Sponsored Links