Confused About a 'Stack Overflow' Post

  • (2 Pages)
  • +
  • 1
  • 2

20 Replies - 2871 Views - Last Post: 08 July 2014 - 10:30 AM

#1 Viper2KX  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 195
  • Joined: 25-January 09

Confused About a 'Stack Overflow' Post

Posted 06 July 2014 - 02:56 PM

I'm probably tip toeing on the line for asking for the code, but I have no idea how to make absolute heads/tails from
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.

Quote

PS - It is hot and humid, I'm slightly cranky. Keeping myself hydrated is near impossible. No AC, & fans are crap.
All I ask is things are made easy for me today.

This post has been edited by Viper2KX: 06 July 2014 - 04:12 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Confused About a 'Stack Overflow' Post

#2 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5522
  • View blog
  • Posts: 11,830
  • Joined: 02-June 10

Re: Confused About a 'Stack Overflow' Post

Posted 06 July 2014 - 03:33 PM

Generally rule of thumb: The ID is almost always the PK, and AI and not null.

In your situation, if the ID weren't the PK, what would be?
Was This Post Helpful? 3
  • +
  • -

#3 Viper2KX  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 195
  • Joined: 25-January 09

Re: Confused About a 'Stack Overflow' Post

Posted 06 July 2014 - 03:59 PM

I really don't know.
I'm not in the mood to guess, and I've spent the last 72 hours something more complete - but this was the closest thing I could find that is very close to completion.

Your answer helps, but it doesn't answer my main call for help on FOREIGN KEYs.
Was This Post Helpful? 0
  • +
  • -

#4 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5522
  • View blog
  • Posts: 11,830
  • Joined: 02-June 10

Re: Confused About a 'Stack Overflow' Post

Posted 06 July 2014 - 04:09 PM

Well, then maybe quit rambling so much and get to the question.
Because what I read in your post asked about the Auto Increment and Primary key

Quote

should the id column also be set to ai and pk?


We all have lives and we've all spent the last 72 hours doing something.
I'm not in the mood to guess either - especially guessing about what someone else's question really is.

Best of luck on the project.

<unsubscribing from thread>
Was This Post Helpful? 2
  • +
  • -

#5 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6063
  • View blog
  • Posts: 23,516
  • Joined: 23-August 08

Re: Confused About a 'Stack Overflow' Post

Posted 06 July 2014 - 04:25 PM

http://www.dreaminco...-normalization/
Was This Post Helpful? 2
  • +
  • -

#6 Viper2KX  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 195
  • Joined: 25-January 09

Re: Confused About a 'Stack Overflow' Post

Posted 06 July 2014 - 04:30 PM

View PostJackOfAllTrades, on 06 July 2014 - 06:25 PM, said:


So I don't need to declare FOREIGN KEY?
I just need to copy/paste the IDs I need into the tables for a relationship?
Was This Post Helpful? 0
  • +
  • -

#7 Viper2KX  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 195
  • Joined: 25-January 09

Re: Confused About a 'Stack Overflow' Post

Posted 07 July 2014 - 09:24 AM

I found: suggested database schema for photos website

And to the best of my ability to create relations, while hoping I could just find a free SQL script to use and modify to my needs.

I had came up with what you see below:
CREATE TABLE IF NOT EXISTS img (
    imgId int unsigned NOT NULL AUTO_INCREMENT,
    imgName_N varchar(50) NOT NULL,
    imgName_O varchar(100) NOT NULL,
    imgDesc varchar(255) NOT NULL,
    imgMime varchar(50) NOT NULL,
    imgSize bigint(20) unsigned NOT NULL,
    imgUploaded date NOT NULL,
    PRIMARY KEY (imgId),
    INDEX (albumId),
    FOREIGN KEY (albumId)
        REFERENCES album (albumId)
        ON DELETE NO ACTION on UPDATE NO ACTION
)  ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS album (
    albumId int unsigned NOT NULL AUTO_INCREMENT,
    albumTitle varchar(75) NOT NULL,
    albumDesc varchar(255) NOT NULL,
    albumUpdated datetime NOT NULL,
    PRIMARY KEY (albumID)
)  ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS mediums (
    mediumsId int unsigned NOT NULL AUTO_INCREMENT,
    mediumsName varchar(50) NOT NULL,
    PRIMARY KEY (mediumsId)
)  ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS img_mediums (
    img_medId int unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (img_medId),
    INDEX (imgId),
    FOREIGN KEY (imgId)
        REFERENCES img (imgId)
        ON DELETE NO ACTION ON UPDATE NO ACTION,
    INDEX (mediumsId),
    FOREIGN KEY (mediumsId)
        REFERENCES mediums (mediumsId)
        ON DELETE NO ACTION ON UPDATE NO ACTION
)  ENGINE=InnoDB;



My question is; do I need to change a thing before I execute these queries?
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9204
  • View blog
  • Posts: 34,581
  • Joined: 12-June 08

Re: Confused About a 'Stack Overflow' Post

Posted 07 July 2014 - 09:32 AM

03	    imgName_N varchar(50) NOT NULL,
04	    imgName_O varchar(100) NOT NULL,


Any clue on what the _N or _O means? If it is not evident in the name then either remove that or write it out.

20	    albumUpdated datetime NOT NULL,


It maybe worth to have a 'date entered' here as well.

09	    PRIMARY KEY (imgId),
10	    INDEX (albumId),
11	    FOREIGN KEY (albumId)

Can an Image be in two different albums at the same time? What if there are no albums to gather it under and it is just free standing? Is there a default, implied, album?



Quote

s, while hoping I could just find a free SQL script to use and modify to my needs.

Why not give pause for an hour or so and looking into relational database intros on the solid off chance you'll pick it up and no longer need to scrounge code deposited elsewhere and attempt to modify for yourself?
Was This Post Helpful? 2
  • +
  • -

#9 ArtificialSoldier  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 387
  • View blog
  • Posts: 1,409
  • Joined: 15-January 14

Re: Confused About a 'Stack Overflow' Post

Posted 07 July 2014 - 10:03 AM

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;


That doesn't make sense, you're trying to do too much with the id column. A column that is both a primary and a foreign key means a 1-to-1 relationship: an album can have one photo, and a photo can only be in one album. That table definition says that the photo ID must be the same as the album ID. If you have multiple photos in an album, then obviously all of them can't have the same ID as the album. Create another column, called album, or album_id, or whatever, and make that the foreign key to the albums table. That gives you a 1-to-many relationship, an album can have many photos, and each photo is in one album. If you want a many-to-many relationship, where each photo can also be in many albums, then you need a third table for that relationship.

Also, it's never necessary to create an index on a primary key column. A primary key is already an index, creating another one on just that column doesn't accomplish anything good.
Was This Post Helpful? 1
  • +
  • -

#10 Viper2KX  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 195
  • Joined: 25-January 09

Re: Confused About a 'Stack Overflow' Post

Posted 07 July 2014 - 12:16 PM

View Postmodi123_1, on 07 July 2014 - 11:32 AM, said:

03	    imgName_N varchar(50) NOT NULL,
04	    imgName_O varchar(100) NOT NULL,


Any clue on what the _N or _O means? If it is not evident in the name then either remove that or write it out.

This doesn't have any relevance.
But since you asked, the "_N" is a field of what the user inputs into a textbox - and the "_O" is the original file name.

Say for example, say Michelangelo decided to do the Sistine Chapel ceiling in GIMP. And upon save he just typed in a lot of letters for the file name, that would go into the field imgName_O. And later when he gets around to uploading it to a website, he can type in the name Sistine Chapel into a textbox and that value would go under imgName_N.

I have PHP script already written to display the image referring to imgName_O. Granted I did need to ask for help and I got the help a few months ago.

View Postmodi123_1, on 07 July 2014 - 11:32 AM, said:

20	    albumUpdated datetime NOT NULL,


It maybe worth to have a 'date entered' here as well.

I already have PHP script plan to execute the query NOW() in that column to show a progressive state.

View Postmodi123_1, on 07 July 2014 - 11:32 AM, said:

09	    PRIMARY KEY (imgId),
10	    INDEX (albumId),
11	    FOREIGN KEY (albumId)

Can an Image be in two different albums at the same time? What if there are no albums to gather it under and it is just free standing? Is there a default, implied, album?

This I can honestly say I have no real clue. I was following along with what the author Larry Ullman did as an example in one of his books released back in 2011.



View Postmodi123_1, on 07 July 2014 - 11:32 AM, said:

Quote

s, while hoping I could just find a free SQL script to use and modify to my needs.

Why not give pause for an hour or so and looking into relational database intros on the solid off chance you'll pick it up and no longer need to scrounge code deposited elsewhere and attempt to modify for yourself?

I thought I had a good intro with the books I own - but seems they were just a waste of ~$50, which do cover PHP 5 and MySQL 5.

I honestly cannot make head or tails of what I'm reading in the MySQL docs.

View PostArtificialSoldier, on 07 July 2014 - 12:03 PM, said:

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;


That doesn't make sense, you're trying to do too much with the id column. A column that is both a primary and a foreign key means a 1-to-1 relationship: an album can have one photo, and a photo can only be in one album. That table definition says that the photo ID must be the same as the album ID. If you have multiple photos in an album, then obviously all of them can't have the same ID as the album. Create another column, called album, or album_id, or whatever, and make that the foreign key to the albums table. That gives you a 1-to-many relationship, an album can have many photos, and each photo is in one album. If you want a many-to-many relationship, where each photo can also be in many albums, then you need a third table for that relationship.

Also, it's never necessary to create an index on a primary key column. A primary key is already an index, creating another one on just that column doesn't accomplish anything good.

This was just an example I tried to do in the link I had provided in the same post to get input to see if I have a decent grasp on things. But I guess I don't
Was This Post Helpful? 0
  • +
  • -

#11 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9204
  • View blog
  • Posts: 34,581
  • Joined: 12-June 08

Re: Confused About a 'Stack Overflow' Post

Posted 07 July 2014 - 12:29 PM

Quote

I already have PHP script plan to execute the query NOW() in that column to show a progressive state.

That is not necessarily responsive to my comment about having a static 'date entered' column, but okay.


Quote

I thought I had a good intro with the books I own - but seems they were just a waste of ~$50, which do cover PHP 5 and MySQL 5.

I honestly cannot make head or tails of what I'm reading in the MySQL docs.


You need to be focusing on relational database design, but I am unclear what books you are referring to.

http://www.amazon.co...=UTF8&node=3789
http://www.amazon.co.../dp/0596008643/
http://www.amazon.co.../dp/1449328016/
Was This Post Helpful? 1
  • +
  • -

#12 Viper2KX  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 195
  • Joined: 25-January 09

Re: Confused About a 'Stack Overflow' Post

Posted 07 July 2014 - 04:05 PM

I read content, I can't understand said content.
I need to see real code that works, I'm that type of visual learner that absorbs what I see.

All I see is just text with no clear or functioning visual aid that gets explained step by step.

Code that works, I'm not even asking for the exact missing pieces I am looking for.
Which is why, modi123_1, I have been trying to find a schema or script that does work so I can modify it to my needs so I can understand all of this better.
Was This Post Helpful? 0
  • +
  • -

#13 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10566
  • View blog
  • Posts: 39,107
  • Joined: 27-December 08

Re: Confused About a 'Stack Overflow' Post

Posted 07 July 2014 - 06:27 PM

It looks to me like your problem is a design issue, not a code issue. Once you have a solid design down, writing the SQL code from the table structures in your design is pretty straight-forward. It's all syntax, which is pretty well documented. I second modi123_1's recommendation on database design books.

Quote

I thought I had a good intro with the books I own - but seems they were just a waste of ~$50, which do cover PHP 5 and MySQL 5.

PHP + MySQL books are generally more PHP geared. They teach you how to use PHP to interact with the database and execute some basic queries. They don't always get more involved with structure and database design.

Quote

I'm probably tip toeing on the line for asking for the code

It's not so much about giving out code here, but it seems like you're asking us to design your project. As I said before, taking a design a creating tables from said design isn't too difficult. It's also one of those things where there generally aren't tutorials on "how do I complete my project?"

I strongly encourage you to spend some time with Atli's tutorial to which JackOfAllTrades provided a link.

I'll offer a bit on database design as well. If you have any OOP experience, database design will start to make sense quickly.

A table describes an object/entity or some sort of relation. It can do both as well. A photo is clearly an object. So what sort of attributes does it have? You might have file_path, name, date_created, size, and owner. Each photo also needs a unique identifier. This is the primary key, which is generally some AUTO_INCREMENT field which we call photo_id.

We've mentioned owner in the photos table. This suggests a relation. So let's design a users table. What are some attributes of a user? We have user_id, username, hashed_password, user_email, and user_join_date to start. This seems like a fairly reasonable way to model a user.

Now let's talk about the relation between photo and user. What do you think the owner field in the photos table should be? Since there is a relation, it seems the owner should reference users.user_id. This would be a foreign key constraint on the photos table.

Now we want to group photos as well into albums, right? So let's start designing an album. What are some attributes of an album? We have album_id (our PK), album_name, album_owner, and album_creation_date. Seems pretty reasonable, right? We haven't talked about how an album contains its pictures. In a language like PHP or Javascript, we'd use an array to store the multiple pictures contained by the album. In database design, this is not good practice. If you have more than one piece of data in a cell, you are doing it wrong. Instead, we design a linking table. We relate albums to photos as follows:
albums_to_photos
-----------------
album_id (PK, FK) | photo_id (PK, FK)



Notice how album_id and photo_id in albums_to_photos both reference the respective fields of the albums and photos tables. Notice as well that I have two PKs here. This is called a composite key. In this way, if I have three photos belonging to album 1 and two photos belonging to album 2, I may have entries:
albums_to_photos
----------------
1 | 1
1 | 2
1 | 3
2 | 4
2 | 5




This should hopefully help you think about design some more. I don't know the ins and outs of your project, so this might not be 100% applicable. It should give you a start though. And rather than me providing code to create these tables, see if you could do it yourself based on the design. Like I've said- if you understand the design, creating the tables using SQL is the easy part.
Was This Post Helpful? 3
  • +
  • -

#14 Viper2KX  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 195
  • Joined: 25-January 09

Re: Confused About a 'Stack Overflow' Post

Posted 08 July 2014 - 08:07 AM

What so, I have the img table and the album table.
imgId is PK
albumId is PK


To call the albumId into the img table, do I just write albumId in that table or do I have to write out the entire FOREIGN KEY string in the img table to get the album info?

That is what I'm trying to get an answer to with the proper code set up.

I've looked at Atli's tutorial that JackOfAllTrades shared. And I still don't fully understand.
Was This Post Helpful? 0
  • +
  • -

#15 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9204
  • View blog
  • Posts: 34,581
  • Joined: 12-June 08

Re: Confused About a 'Stack Overflow' Post

Posted 08 July 2014 - 08:16 AM

Quote

To call the albumId into the img table, do I just write albumId in that table or do I have to write out the entire FOREIGN KEY string in the img table to get the album info?

That made no sense.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2