suggested database schema for photos website
I already got the databases the way I want them typed up in the QUERY editor in MySQL Workbench to get imported for the live site this is for. But its the FOREIGN KEYs I have slight issues with.
In the link, for the Photo_Tag table - should the id column also be set to ai and pk?
PLEASE HELP ME WITH THE FOREIGN KEY FIRST.
READ WHOLE POST, DON'T JUST LOOK FOR THE QUESTION MARKS.
I honestly don't want this to get into a long discussion, a couple posts - but I just need to see what the correct code is so I can know the next time.
So, here is my off the top of my head attempt:
CREATE TABLE IF NOT EXISTS Photos ( id int unsigned not null auto_increment, title varchar(75) not null, url varchar(125) not null, description text not null, primary key (id), index album (id), foreign key (id) references Ablums (id) on delete cascade ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS Albums ( id int unsigned not null auto_increment, title varchar(75) not null, description text not null ) ENGINE=InnoDB; -- unsure about next 2 tables CREATE TABLE IF NOT EXISTS Tags ( id int unsigned not null auto_increment, name varchar(30) not null, primary key (id) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS Photo_Tag ( id int unsigned not null -- make ai and pk? ) ENGINE=InnoDB;
*** Not my actual code, just a template I have hopefully found that will work
... ok, I lost my train of thought.
All I ask is things are made easy for me today.
This post has been edited by Viper2KX: 06 July 2014 - 04:12 PM