Many to Many cOnFuSiOn

Discussion in 'ASP General' started by the other john, Feb 15, 2006.

  1. 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!!
    the other john, Feb 15, 2006
    #1
    1. Advertising

  2. 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.
    Bob Barrows [MVP], Feb 15, 2006
    #2
    1. Advertising

  3. 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
    the other john, Feb 15, 2006
    #3
  4. 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 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
    --
    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], Feb 15, 2006
    #4
  5. 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?
    the other john, Feb 15, 2006
    #5
  6. 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.
    Bob Barrows [MVP], Feb 15, 2006
    #6
  7. 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.
    the other john, Feb 15, 2006
    #7
  8. 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], Feb 15, 2006
    #8
  9. 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.
    the other john, Feb 15, 2006
    #9
  10. 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.
    Bob Barrows [MVP], Feb 15, 2006
    #10
  11. 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.
    the other john, Feb 15, 2006
    #11
  12. "the other john" <> wrote in message
    news:...
    > 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".
    Chris Hohmann, Feb 22, 2006
    #12
  13. Chris Hohmann wrote:
    > "the other john" <> wrote in message
    > news:...
    >> 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".


    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.
    Bob Barrows [MVP], Feb 22, 2006
    #13
  14. >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.
    Anthony Jones, Feb 23, 2006
    #14
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Marco Ippolito
    Replies:
    0
    Views:
    2,578
    Marco Ippolito
    Oct 11, 2004
  2. =?Utf-8?B?U3R1?=

    session vars how many is to many ?

    =?Utf-8?B?U3R1?=, Mar 5, 2005, in forum: ASP .Net
    Replies:
    5
    Views:
    335
  3. dee
    Replies:
    2
    Views:
    398
  4. Gary Cohan

    Many to Many Collections

    Gary Cohan, Oct 31, 2003, in forum: Java
    Replies:
    4
    Views:
    441
    Chris Riesbeck
    Nov 3, 2003
  5. Eric Ford
    Replies:
    2
    Views:
    584
    Michael Meyer
    Jan 29, 2004
Loading...

Share This Page