I need help with MySQL relations (Logic)

Hello all,

I just need a little help with logic!
I want to display in the admin panel "product uploaded by: " feature. So I figured I need a one-to-one relationship of the columns.

I created 2 Tables (Products, Admins)

Products:
productUploader

Admins:
adminUsername

Now if I’m not mistaken, I want to connect the product to the admin, not the admin to the product.

What should I do in phpMyAdmin?
make adminUsername or productUploader unique?

or connect productUploader to Admins primary id key?

This would only be true if a person can only upload a single product.

What i imagine you’re ACTUALLY describing is a one-to-many relation:

A product is uploaded by a single person;
A person can upload many products.

1 Like

There are, effectively, three types of relations:

1-1:
At this point, your data might as well be columns in the original table, but are separated for organizational reasons or… I don’t know really.
Example:
“I have a person table, and a license table. One person can have only 1 license, and each license can belong to only 1 person.”
(Which is when someone goes “so why not put the license info in the person table”, and you scratch your head coming up with an answer.)

1-N:
The classic relationship. One something can have many somethings.
Example:
One author can make many posts on a forum; a given post can only have 1 author.

N-N:
A many to many relationship. These should get broken down by a join table into two 1-N relations.
Example: I have a table of students, and a table of teachers. Each student has multiple teachers in a day, and each teacher teaches multiple students.
(This then should break down into a join table on teacher-student pairings, probably including such information as what time of day the student has the teacher, or what year, etc.)

1 Like

yes to adminUsername, no to productUploader (see @m_hutley’s reply)

yes

1 Like

Ok, I made adminUsername unique.

I tried to connect Admins primary id to productUploader but it gives me an error…

please advise.

The admin primary key is an integer and productUploader is a string. All you need is a basic foreign key relationship. Make productUploader an integer. If you are using InnoDb or a table that supports foreign keys use actual foreign keys.Extra advice don’t use a name like productUploader. That sounds really stupid. Just call it creator_id. Also don’t prefix column names with product when the table is products. Same for admin. Why do you need to prefix the admin table columns with admin. I never understand why devs feel the need to prefix the column names with the table name.

1 Like

because they don’t want to apply column aliases in sql

i don’t agree with it, but i understand it

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.