SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2005
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    complex tables/relationships for pro wrestling...

    I usually work on the simple tables and basic relationships. But the current project, I'm workin' on for the local pro wrestling company, needs the complex tables and relationships and use alot of join functions to pull info from there. Moreover, I only can use up to 5 tables which make things more tough for me and with my lmited experience on advanced tables/relationships, to figure the best approach to the table designs and relationships model. So I am seeking for your advice and suggestion for the practiceable model based on rough information below:

    -news: general news, next event news and past event reports, headline image paths, headline titles etc
    -past event: event theme (name of event), past event reports, card results, link to related image/video gallery, headline image paths etc
    -next event: event theme, next event news, lineup card, date, location etc
    -articles: titles, articles, headline image paths etc
    -interviews: titles interviews, image paths etc
    -rosters: list of wrestlers, managers, referees and announcers (use image and name to the rosters and act as link to the roster bio)
    -roster bio: bio, height, weight etc and related image and video gallery
    -multimedia: image, video and wallpaper paths, ALT descriptions etc

    Long one eh? Thanks for any help in advance!

    Jace

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    only 5 tables? huh? what's that all about?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Mar 2005
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah that's what I was told that I only can have up to 5 tables.

    Jace

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    there is a limit, but i think it's more like 64. if those tables are composed of view using the merge method, then the joins in those view add up to that total. so if those view have an average of 12.8 tables per view, then 5 joins would be the maximum.

    but i doubt that's the case here.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    maybe it's an internet provider who has set an arbitrary control panel limit of 5 to prevent proliferation of files -- doesn't mysql store one file per table on disk?

    yay

    well, jace, good luck, you may have some luck with the "one true lookup table" concept (google for that exact phrase), which i normally do not recommend
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Odd. Who set the limit? If it was a ISP then time to move imo.

    For MyISAM tables I believe it stores atleast 2 per table, 1 for the description and another for data, and possibly another for indexes. InnoDB uses less, 1 description, and then 1 big shared file for everything else, if I remember correctly.

  7. #7
    SitePoint Zealot
    Join Date
    Mar 2005
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Today I convinced the client to give me the access to the server to investigate the issue further and oh boy I found out it's actually 5 mysql databases and not 5 tables. Look like they mistook that we only can go up to 5 tables.

    It should be sufficent to meet the website's needs. I'll work on the table designs/relationships and then post them here for feedback/suggestion.

    I apologize for drag on this thread and appreciate your replies.

    Jace

  8. #8
    SitePoint Zealot
    Join Date
    Mar 2005
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have developed the rough draft database relations, but I know that I missed something to bridge the news to three tables (reports, past_event and next_event). Any advice?

    page contents
    news: pull news from past_event, reports and next_event tables
    past_event: pull info from past_event such as event results, event news etc
    next_event, pull info from next_event such as event updates, videos, lineup card etc
    reports: pull info from reports such as general news
    page_type: news.php, past_event.php etc

    news
    news_id

    past_event
    id
    page_type_id
    news_id

    next_event
    id
    event_id
    page_type_id
    news_id

    page_type
    page_type_id
    page_type

    I have some questions about video/gallery issue.

    1. Each event could bring in 10, 20, 50 or even less or more video/simages. So I searched the forum and can't find threads show how to deal or best way to approach the big gallery issue. Anybody mind to point me to right thread or link that may be relavant to the issue.

    2. Is it no-no to have two or more fields with same value name? For instance, an image has more than one wrestler, then I want to assign two or more wrestler_id to this image.


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
  •