If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > Needing Help with a Design

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 07-09-11, 20:59
dcolum23 dcolum23 is offline
Registered User
 
Join Date: Jul 2011
Posts: 2
Question 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.
Reply With Quote
  #2 (permalink)  
Old 07-10-11, 07:11
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
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 ) 
);
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3 (permalink)  
Old 07-10-11, 16:44
dcolum23 dcolum23 is offline
Registered User
 
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.
Reply With Quote
Reply

Tags
design, designpattern

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On