Tech Off Post

Single Post Permalink

View Thread: Data model performance question (splitting tables)
  • User profile image
    Aptly Goodie

    Is it a good idea to split tables? Let's say I have a table of photos, a table of galleries, a table to relate photos to galleries (M-to-N), a table of articles, and a table to relate photos to articles (M-to-N). Should I combine these two relation tables into one and distinguish each relation with a type, or split it into two by type? If I split them, the growth of table size will only be moderate, say O(1.5N), knowing that a photo may only be featured in 1.5 galleries on average (maybe lower). If I combine them, it may make searches lengthier because it will record all relationships, including ones that I have no mentioned, making the growth likely be O(m x n) or maybe O(n^2). So one makes coding harder and overall performance better, the other makes coding easy but performance may be unscaleable. Further, it's very likely that finding photos in a gallery occurs more frequently than finding all relations in a gallery. What should I do?