| |
|
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.
|
 |
03-07-11, 00:54
|
|
Registered User
|
|
Join Date: Oct 2009
Posts: 46
|
|
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
|
|
03-07-11, 00:56
|
|
Registered User
|
|
Join Date: Oct 2009
Posts: 46
|
|
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?
|
Last edited by LAYGO; 03-07-11 at 01:14.
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Design question that's confusing me
