SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard jag5311's Avatar
    Join Date
    Jan 2003
    Location
    Somewhere in Indiana
    Posts
    3,082
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Many to Many relationships=bad database design?

    I was reading my Ben Forta's Coldfusion WACK book and the chapter was general database design. He discussed how often if a database has a many to many relationship, it is often a poor database design and probably won't scale well. The solution is often having multiple one to many relationships.

    Your thoughts on this.

    Bryan

  2. #2
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,236
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Good database design dictates that an associative table or map table is the best solution if you have a many-to-many relationship. For example, if you were setting up a content site that had many authors, and those authors were only allowed to write in certain categories (for example, Joe can write about Sports or Business, but not Fashion, and Sally can write about World Events or Weather, but not Technology), your database tables may look a bit like this:

    Code:
    AUTHOR
    AuthorID
    AuthorName
    
    CATEGORY
    CategoryID
    CategoryName
    Since authors can write to more than one category, and each category will probably have multiple authors, you now have a many-to-many relationship. To remedy this and create multiple one-to-many relationships instead, you'd create another table (for simplicity's sake, let's call this one ACMap):
    Code:
    ACMAP
    AuthorID
    CategoryID
    Each record in ACMap would be its own primary key (a unique combination of AuthorID and CategoryID). This would allow for quicker searching using the map table (for example, in trying to figure out which authors are allowed to write about a particular category), and makes your life coding any applications based on this data structure much easier.

  3. #3
    runat="server" Golgotha's Avatar
    Join Date
    Nov 2001
    Location
    Colorado
    Posts
    2,085
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    a many-to-many relationship IS really 2 one-to-many relationships with a third table usually refered to as a junction table.

  4. #4
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if a database has a many to many relationship, it is often a poor database design and probably won't scale well.
    Very, very true. As suggested by Golgotha you need another table to seperate the relationship;

    The bad thing about M:M is that at some point you will find that you have repeatitive data in the table in question.

    Not very good

  5. #5
    SitePoint Addict Avido's Avatar
    Join Date
    Jul 2003
    Location
    Kortrijk, Belgium, Europe, the world
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you give an example of repeatitive data when having an M:M relationship? I tried to find one, but i can't.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •