Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2009
    Posts
    67

    Angry Design question that's confusing me

    Long time no see. I've got a new (personal) project I'm working on & I've got grandeur ideas that I'm presently capable of designing. I'm working on a social network site around a particular band, but also trying to develop/design in such a way that this could be duplicated for any band's fan site.

    I'm also trying to design the schema in YAML for Doctrine's ORM. I'll include the YAML, the SQL it generates, and the resultant tables. I've decided to let Doctrine define the PKs & I've commented out my PK declarations to see if it'd help (it didn't).

    The problem is mySQL is choking on the constraints every time I try to run the generated SQL. I also can't design the relationships in phpMyAdmin. So, I'm stumped.

    Code:
    Server version: 5.5.8 MySQL Community Server (GPL)
    Code:
    Gig:
      columns:
        # gig_id:        { type: int, notnull: true, autoincrement: true, unique: true, primary: true }
        gigDate:            { type: date, notnull: true }
        venue_name_id:      { type: int, notnull: true }
        venue_city_id:      { type: int, notnull: true }
        venue_state_id:     { type: int, notnull: true }
        song_list_name_id:  { type: int, notnull: true }
        poster_id:          { type: int }
      relations:
        VenueName:
          foreignType:  one
          local:        venue_name_id
          foreign:      id
        VenueCity:
          foreignType:  one
          local:        venue_city_id
          foreign:      id
        VenueState:
          foreignType:  one
          local:        venue_state_id
          foreign:      id
        songListName:
          foreignType:  one
          local:        song_list_name_id
          foreign:      id
        
    ReleaseName:
      columns:
        # release_id:   { type: int, notnull: true, autoincrement: true, unique: true, primary: true }
        releaseName:        { type: string(255), notnull: true }
        releaseDate:        { type: date, notnull: true }
        artist_id:          { type: int, notnull: true }
        label_id:           { type: int, notnull: true }
        song_list_name_id:  { type: int, notnull: true }
      relations:
        Artist:
          foreignType:  one
          local:        artist_id
          foreign:      id
        Label:
          foreignType:  one
          local:        label_id
          foreign:      id
        SongListName:
          foreignType:  one
          local:        song_list_name_id
          foreign:      id
        
    SongList:
      columns:
        song_list_name_id: { type: int, notnull: true }
        song_name_id:      { type: int, notnull: true }
      relations:
        SongName:
          local:        song_name_id
          foreign:      id
        SongListName:
          local:        song_list_name_id
          foreign:      id
        
    Opener:
      columns:
        gig_id:       { type: int, notnull: true }
        artist_id:    { type: int, notnull: true }
      relations:
        Gig:
          foreignType:  one
          local:        gig_id
          foreign:      id
        Artist:
          foreignType:  one
          local:        artist_id
          foreign:      id
         
    # etching
    ReleaseFormatList:
      columns:
        release_name_id:    { type: int, notnull: true }
        release_format_id:  { type: int, notnull: true }
        formatNote:         { type: string(255), notnull: true }
      relations:
        ReleaseName:
          local:        release_name_id
          foreign:      id
          class:        
        ReleaseFormat:
          foreignType:  one
          local:        release_format_id
          foreign:      id
        
    ReleaseFormat:
      columns:
        # format_id:    { type: int, notnull: true, autoincrement: true, unique: true, primary: true }
        formatName:   { type: string(255), notnull: true }
        
    Artist:
      columns:
        # artist_id:    { type: int, notnull: true, autoincrement: true, unique: true, primary: true }
        artistName:   { type: string(255), notnull: true }
        
    Label:
      columns:
        # label_id:     { type: int, notnull: true, autoincrement: true, unique: true, primary: true }
        labelName:    { type: string(255), notnull: true }
    
    VenueName:
      columns:
        # venue_id:     { type: int, notnull: true, autoincrement: true, unique: true, primary: true }
        venueName:    { type: string(255), notnull: true }
    
    VenueCity:
      columns:
        # venueCity_id: { type: int, notnull: true, autoincrement: true, unique: true, primary: true }
        venueCity:    { type: string(255), notnull: true }
        
    VenueState:
      columns:
        # venueState_id: { type: int, notnull: true, autoincrement: true, unique: true, primary: true }
        venueState:    { type: string(255), notnull: true }
        
    SongName:
      columns:
        # song_id:      { type: int, notnull: true, autoincrement: true, unique: true, primary: true }
        songName:     { type: string(255), notnull: true }
      relations:
        SongList:
          local:      id
          foreign:    song_name_id
          class:      SongListName
          refClass:   SongList
        
    SongListName:
      columns:
        # songListName_id: { type: int, notnull: true, autoincrement: true, unique: true, primary: true }
        songListName:    { type: string(255), notnull: true }
      relations:
        SongName:
          local:      id
          foreign:    song_list_name_id
          class:      SongName
          refClass:   SongList
    Code:
    CREATE TABLE artist (id BIGINT AUTO_INCREMENT, artistname VARCHAR(255) NOT NULL, PRIMARY KEY(id)) ENGINE = INNODB;
    CREATE TABLE format_note (id BIGINT AUTO_INCREMENT, release_name_id INT NOT NULL, release_format_id INT NOT NULL, formatnote VARCHAR(255) NOT NULL, INDEX release_name_id_idx (release_name_id), INDEX release_format_id_idx (release_format_id), PRIMARY KEY(id)) ENGINE = INNODB;
    CREATE TABLE gig (id BIGINT AUTO_INCREMENT, gigdate DATE NOT NULL, venue_name_id INT NOT NULL, venue_city_id INT NOT NULL, venue_state_id INT NOT NULL, song_list_id INT NOT NULL, poster_id INT, INDEX venue_name_id_idx (venue_name_id), INDEX venue_city_id_idx (venue_city_id), INDEX venue_state_id_idx (venue_state_id), PRIMARY KEY(id)) ENGINE = INNODB;
    CREATE TABLE label (id BIGINT AUTO_INCREMENT, labelname VARCHAR(255) NOT NULL, PRIMARY KEY(id)) ENGINE = INNODB;
    CREATE TABLE opener (id BIGINT AUTO_INCREMENT, gig_id INT NOT NULL, artist_id INT NOT NULL, INDEX gig_id_idx (gig_id), INDEX artist_id_idx (artist_id), PRIMARY KEY(id)) ENGINE = INNODB;
    CREATE TABLE release_format (id BIGINT AUTO_INCREMENT, formatname VARCHAR(255) NOT NULL, PRIMARY KEY(id)) ENGINE = INNODB;
    CREATE TABLE release_name (id BIGINT AUTO_INCREMENT, releasename VARCHAR(255) NOT NULL, releasedate DATE NOT NULL, artist_id INT NOT NULL, label_id INT NOT NULL, song_list_id INT NOT NULL, INDEX artist_id_idx (artist_id), INDEX label_id_idx (label_id), INDEX song_list_id_idx (song_list_id), PRIMARY KEY(id)) ENGINE = INNODB;
    CREATE TABLE song_list (id BIGINT AUTO_INCREMENT, song_list_name_id INT NOT NULL, song_name_id INT NOT NULL, INDEX song_name_id_idx (song_name_id), INDEX song_list_name_id_idx (song_list_name_id), PRIMARY KEY(id)) ENGINE = INNODB;
    CREATE TABLE song_list_name (id BIGINT AUTO_INCREMENT, songlistname VARCHAR(255) NOT NULL, PRIMARY KEY(id)) ENGINE = INNODB;
    CREATE TABLE song_name (id BIGINT AUTO_INCREMENT, songname VARCHAR(255) NOT NULL, PRIMARY KEY(id)) ENGINE = INNODB;
    CREATE TABLE venue_city (id BIGINT AUTO_INCREMENT, venuecity VARCHAR(255) NOT NULL, PRIMARY KEY(id)) ENGINE = INNODB;
    CREATE TABLE venue_name (id BIGINT AUTO_INCREMENT, venuename VARCHAR(255) NOT NULL, PRIMARY KEY(id)) ENGINE = INNODB;
    CREATE TABLE venue_state (id BIGINT AUTO_INCREMENT, venuestate VARCHAR(255) NOT NULL, PRIMARY KEY(id)) ENGINE = INNODB;
    ALTER TABLE format_note ADD CONSTRAINT format_note_release_name_id_release_name_id FOREIGN KEY (release_name_id) REFERENCES release_name(id);
    ALTER TABLE format_note ADD CONSTRAINT format_note_release_format_id_release_format_id FOREIGN KEY (release_format_id) REFERENCES release_format(id);
    ALTER TABLE gig ADD CONSTRAINT gig_venue_state_id_venue_state_id FOREIGN KEY (venue_state_id) REFERENCES venue_state(id);
    ALTER TABLE gig ADD CONSTRAINT gig_venue_name_id_venue_name_id FOREIGN KEY (venue_name_id) REFERENCES venue_name(id);
    ALTER TABLE gig ADD CONSTRAINT gig_venue_city_id_venue_city_id FOREIGN KEY (venue_city_id) REFERENCES venue_city(id);
    ALTER TABLE opener ADD CONSTRAINT opener_gig_id_gig_id FOREIGN KEY (gig_id) REFERENCES gig(id);
    ALTER TABLE opener ADD CONSTRAINT opener_artist_id_artist_id FOREIGN KEY (artist_id) REFERENCES artist(id);
    ALTER TABLE release_name ADD CONSTRAINT release_name_song_list_id_song_list_id FOREIGN KEY (song_list_id) REFERENCES song_list(id);
    ALTER TABLE release_name ADD CONSTRAINT release_name_label_id_label_id FOREIGN KEY (label_id) REFERENCES label(id);
    ALTER TABLE release_name ADD CONSTRAINT release_name_artist_id_artist_id FOREIGN KEY (artist_id) REFERENCES artist(id);
    ALTER TABLE song_list ADD CONSTRAINT song_list_song_name_id_song_name_id FOREIGN KEY (song_name_id) REFERENCES song_name(id);
    ALTER TABLE song_list ADD CONSTRAINT song_list_song_list_name_id_song_list_name_id FOREIGN KEY (song_list_name_id) REFERENCES song_list_name(id);
    Anytime I try to use phpMyAdmin designer to define the PK/FK, I get "Error: relation not defined".

    And ULTIMATELY, I'd like to see something like this:
    Code:
    Album/Release/Record
    - has one label
    - has one artist
    - has many songs
    - has many pictures
    - has many format types
    - has many tags
      
    Compilation
    - has one label
    - has many artists
    - has many songs
    - has many pictures
    - has many formats
    
    FormatType
    - has one type (12" vinyl, etc)
    
    ReleaseFormatNote
    - has one release
    - has one format
    - has one note
    
    Show/Gig/Concert
    - has one/many artists
    - has one venue
    - has one city
    - has one state
    - has one date
    - has one setlist
    - has many pictures
    - has many tags
    
    Photos/Pictures
    - has one path to image
    - has one rating
    - has many comments
    - has many tags
    
    Comments
    - has one author
    - has one comment
    - has one rating
    
    Tags
    - has one tag name
    - has many things it tags
    Attached Thumbnails Attached Thumbnails bhsdb-erd.png  

  2. #2
    Join Date
    Oct 2009
    Posts
    67
    And the mySQL error:
    Code:
    SQLSTATE[HY000]: General error: 1005 Can't create table 'xxxxxxx.#sql-d40_7c' (e
    rrno: 150). Failing Query: "ALTER TABLE format_note ADD CONSTRAINT format_note_r
    elease_name_id_release_name_id FOREIGN KEY (release_name_id) REFERENCES release_
    name(id)". Failing Query: ALTER TABLE format_note ADD CONSTRAINT format_note_rel
    ease_name_id_release_name_id FOREIGN KEY (release_name_id) REFERENCES release_na
    me(id)
    Well, I feel like a goob for not searching on the error sooner:
    SQLSTATE[HY000]: General error: 1005 Can't create table '*' (errno: 150) - Mirmo Dynamics

    When things are right, they work, EH?

    Still gotta add photos/tags/comments to the whole shebang. Do I add a type table (gig/release/photo) & a comment/tag will have a FK & Type to associate it?
    Attached Thumbnails Attached Thumbnails bhsdb-erd-better.png  
    Last edited by LAYGO; 03-07-11 at 02:14.

Posting Permissions

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