Understanding Primary Key and Relationship Databases with MySQL (phpmyadmin)

Question

Forewarning - Something could be wrong from the start. More than one thing. Any additional help outside of the question I ask would be much appreciated :)

Also, admin_tbl can be ignored. It is simply usernames/passwords for administrators for the site.

Aim (and question): To create two relationship tables. Using my HTML Form I want to be able to insert information such as:

albumName - Exploration
name - Dead Sea
timeDate - 2015-12-21
comment - A beautiful landscape of the Dead Sea in Israel.
Upload Image - (button to choose image)

I have managed to create code (php and sql files) to upload the information into the database and upload the image into a 'Uploads' folder. However, the aim is for each album to have an ID (albumID), and to be able to have multiple images be uploaded and be a part of one album (along with having multiple albums of course, each with different images in). Can you help me do this?

What I have so far:

'image_tbl' - - Table which stores image path and the information from the form (including Album Name)

'album_tbl' - - Table which stores albumID and then albumName

Problems (I have run into):

image_tbl - As you can see each image I insert into the database is given an id, which is auto-incremented. Apparently, the auto-incremented id (unsure of official term) has to be the Primary Key. However, to have albumName (in this table) have a relationship in album_tbl doesn't it need to be the Primary Key?

album_tbl - In this table I have albumID and albumName. What I want is that when a form is inserted with an albumName (e.g. 'Exploration), for it to be also given the albumID. Therefore if I printed a specific 'albumID' every image placed into that would be shown.

Hope everything is understandable.

P.s. If you need any additional information just ask, and if you have recommendations to do this a lot easier/more efficiently, just tell me!

P.p.s If it can quickly be done, can someone explain what the 'Index' option is in the context of my tables (so it can easily be understood), thanks!

Answer 1

TL;DR You don't need to declare a "relationship" ie foreign key (FK) to query. But its a good idea. When you do, a FK can reference a primary key (PK) or any other UNIQUE column(s).

---

PKs & FKs are erroneously called "relationships" in some methods and products. Application relationships are represented by tables. (Base tables and query results.) PKs & FKs are constraints: they tell the DBMS that only certain situations can arise, so it can notice when you make certain errors. They are not relationships, they are statements true in & of every database state and application situation. You do not need to know constraints to update and query a database.

Just know what every table means. Base tables have DBA-given meanings that tell you what their rows mean. Queries also have meanings that tell you what their rows mean. Query meanings are combined from base table meanings in parallel with how their result values are combined from base table values and conditions.

• image_tbl -- image [Id] is in an album named [albumName], is named [name], is dated [dateTime] & has comment [comment]
• album_tbl -- album [albumID] is named [albumName]

You do not have to declare any PKs/UNIQUEs or FKs! But it is a good idea because then the DBMS can disallow impossible/erroneous updates. A PK/UNIQUE says that a subrow value for its columns must appear only once. A FK says that a subrow value for its columns must appear as a PK/UNIQUE subrow value in its referenced table. The fact that these limitations hold on base tables means that certain limitations hold on query results. But the meanings of those query results are per the query's table & condition combinations, independent of those limitations. Eg whether or not album names are unique,

• image_tbl JOIN album_tbl USING albumName -- image [Id] is in an album named [albumName], is named [name], is dated [dateTime] and has comment [comment] AND album [albumID] is named [albumName]

The only problem here is that if album names are not unique then knowing an image's album's name isn't going to tell you which album it's in; you only know that it's in an album with that name. On the other hand if album names are unique, you don't need album_tbl albumID.

So if album names are unique, declare albumName UNIQUE in album_tbl. Then in image_tbl identify the album by some PK/UNIQUE column of album_tbl. Since album_id is presumably present just for the purpose of identifying albums, normally we would expect it to be chosen. Then in image_tbl declare that column as a FK referencing album_tbl.

PS Indexes generally speed up querying at the cost of some time and space. A primary key declaration in a table declaration automatically declares an index. It's a good idea to index PK, UNIQUE and FK column sets.

Answer 2

Welcome to relational databases.
The idea with relational databases is to have all data relate to each other but to avoid replication of data. This is called Normalization.

In your example you are replicating "Album Name" name in both tables, which is not necessary. What you want to do is change the column Album Name (in image_tbl) to Album ID and add a foreign key on album_id.image_tbl to album_id. album_tbl.