Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011
    Posts
    2

    Question Unanswered: Needing Help with a Design

    Hey all,

    I've been ghosting these forums for a while and finally decided to join. It's time to become a part of the community so I can learn from everyone!

    I have a personal project that I've been working on for a while and I'm having a hard time convincing myself of the right design.

    It's an application that allows users to create Presentations. These presentation have Assets (images, video, links to docs, etc) and these Assets are laid out within an Template. So:

    Presentation has a Template
    Template has Positions (1, 2, 3, etc)
    Template houses Assets in those Positions

    It was suggested that my schema look like this:

    Code:
    table Presentation
    int id
    varchar name
    int fk_template (references template.id)
    ...
    
    table Template
    int id
    varchar name
    ...
    
    table Position
    int id
    int zorder (if it is the 1st, 2nd, 3rd position of the given template)
    int fk_template (references Template.id)
    ...
    
    table Asset
    int id
    varchar name
    ...
    
    table AssetForPresentation
    int fk_asset (references Asset.id)
    int fk_presentation (refernces Presentation.id)
    int fk_position (ferences Position.id)
    How do you guys feel about this? It doesn't sit well with me... It seems so convoluted and I have no idea what the query would be to pull a Presentation, grab the Layout and populate the layout with the right Assets.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would eliminate the positions table
    Code:
    CREATE TABLE templates
    ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , name VARCHAR(99) NOT NULL
    , otherstuff ...
    );
    
    CREATE TABLE presentations
    ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , name VARCHAR(99) NOT NULL
    , templ INTEGER NOT NULL
    , CONSTRAINT pres_templ
        FOREIGN KEY ( templ ) REFERENCES templates ( id ) 
    ); 
    
    CREATE TABLE assets
    ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , name VARCHAR(99) NOT NULL
    , type TINYINT NOT NULL -- 1=img, 2=flv, 3=doc, etc.
    );
    
    CREATE TABLE presentation_assets
    ( pres INTEGER NOT NULL 
    , asset INTEGER NOT NULL
    , zorder TINYINT NOT NULL -- 1=1st, 2=2nd, 3=3rd, etc.
    , PRIMARY KEY ( pres , asset , zorder )
    , CONSTRAINT presasset_pres
        FOREIGN KEY ( pres ) REFERENCES presentations ( id ) 
    , CONSTRAINT presasset_asset
        FOREIGN KEY ( asset ) REFERENCES assets ( id ) 
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2011
    Posts
    2
    r937,

    Thanks for the reply.

    The only potential issue is that there's no reference to how many positions a Template has, before an asset occupies it. I suppose in place of "otherstuff" there could be a "num_positions" (2 or 6) and then just let the UI handle where those positions are.

    I need to be able to load a presentation in my "designer" and add/replace assets in the various positions of the presentation template.

Tags for this Thread

Posting Permissions

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