MySQL architecture

[mactron]

Hello, I have a table with people and some details insde. So, my question is if my MySQL architecture down below is good or not. Should I devide the table into two tables and kept only p_id, p_name and p_slug inside my table (people_tbl). Thank you!

p = person
p_description = bio
p_category = occupation (people_category_tbl)

people_tbl
 

+------+---------+---------+---------------+-------------+-------------------+-----------+------------+---------+
| p_id | p_name  | p_title | p_description | p_seo_title | p_seo_description | p_country | p_category | p_slug  |
+------+---------+---------+---------------+-------------+-------------------+-----------+------------+---------+
|  1   | John Li | title   |  johns bio    | 70 charsets | 170 charsets      | Germany   | 1          | john-li |
|  2   | Bob Stu | title   |  bobs bio     | 70 charsets | 170 charsets      | Italy     | 2          | bob-stu |
+------+---------+---------+---------------+-------------+-------------------+-----------+------------+---------+

 

Edited February 16, 2020 by mactron

[maxxd]

It depends - can people have more than one occupation and/or title in your system? If so, create a couple junction tables for that data. Other than that I'd consider splitting name into first and last just to make searching easier, but the rest looks fine.

[Barand]

Needs a "country" table (country_id/country_name) and store the id of the country (not the name) in your table.

[mactron]

I have three tables, people_tbl, people_category and people_country, but let's get focused on 1st and 2nd table only.

people_tbl = people and their info (please check MySql architecture above)

people_category_tbl = occupation

 Witch my code below, I fetch the data between both tables. All the data are correctly fetched and completely relevant. Actually everything works just fine, but I'm curious If I'm on the right path. is there anything that I can fix, improve.. Should I use only one query with JOIN syntax? Thanks!

func.php

<?php
class PeopleData extends dbh{

    public function getPeople () {

        $sql = "SELECT * FROM people_tbl";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        $result = $stmt->fetchAll((PDO::FETCH_OBJ));
            
        return $result;
    }

public function getPeopleCategoryName() {

        $sql = "SELECT * FROM people_category_tbl";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        $category = $stmt->fetch(PDO::FETCH_OBJ);
            
        if($category == null) {

            return null;
        }else {
            return $category->people_category_name;
        }
    } 
}



fetch.php
 <?php 


   	$people = new PeopleData();
	$peoples = $people->getPeople();

    $category = new PeopleData();

?>

<?php foreach ($peoples as $people) { ?>

<?php 

	echo $people->people_id . " "; 
    echo $people->people_name . " "; 

    echo $category->getPeopleCategoryName($people->people_category) . " "; 
     

?> 

 

Edited February 18, 2020 by mactron

[Barand]

A single query with a join is more efficient than separate queries.

[mactron]

Done with JOINs and work like a charm. Thanks!

Just another question ..  I would like to add photo of each person, so I will have two additional columns inside my persons_tbl (person_avatar_path, person_avatar_name). Is it better to create new table for avatars?

Edited February 19, 2020 by mactron

[chhorn]

I bet you need the avatar more often, so a new table would involve a join or at least a separat query every time. so if your users table does not seem to be flooded with columns anyway, or the user can store multiple avatars, maybe for historical reasons, i would stick on two columns within the users table for performance and maintainability - you still can simply let the column out on queries that don't need the data.

Edited February 19, 2020 by chhorn

[gizmola]

Agree with chhorn:  put in the person table, unless you want the historic record of Avatars.

Hopefully you are using the InnoDB engine for all your MySQL tables.  If not, you can run ALTER TABLE on them to change the engine from the default MyISAM.  

With InnoDB, when you SELECT a row from an InnoDB table by the primary key, the read activity includes all the data anyways, since InnoDB has clustered indexes.  This type of micro-optimization isn't really that useful to consider when thinking about DB design, but you might as well sleep well at night knowing that there's no significant performance cost to putting it into the person table.  

[mactron]

Yeah I'm using InnoDB ..

What about if someday I decided to add likes, dislikes and views for each person. That's mean 3 additional columns inside people_tbl and 13 columns in total. 

I'm worried about JOINS because I already use three JOINS in one query. I have heard people complain that JOINS may be pretty slow.

Thanks!

[Barand]

And what would you do if they have multiple likes, dislikes and views?

Edited February 20, 2020 by Barand

[mactron]

Sorry, but what do you mean with multiple, I would simple update that columns ..

Edited February 20, 2020 by mactron

[Barand]

more ... than ... one.

[mactron]

Ahh I see, another table & another JOIN..

[Barand]

One table for likes , one for dislikes and and a third for views

Perhaps combine like/dislikes with a flag column to distinguish

Edited February 20, 2020 by Barand

[gizmola]

I would certainly have the likes/dislikes in a table as Barand suggested, along with an FK to the user who made the like/dislike rating.  This allows you to do a number of important things, like for example, preventing a person from liking or disliking the same person repeatedly.  

There is a cost to summarizing data like that repeatedly in a sub-query so frequently people will implement a de-normalized summary, or better yet, put the summary in a caching system like Redis.  The summarized(denormalized) totals could be in the person table if you really wanted them to be, but you should still have the detail person_rating table to store the individual like/dislike rows.

[mactron]

I will do that way but I'm worried about too many JOINS. I already have 3 queries with 3 JOINS in each. 

[Barand]

10 minutes ago, mactron said:

with 3 JOINS in each. 

Wow!

[mactron]

7 minutes ago, Barand said:

Wow!

Thanks 😝

 

[gizmola]

Relational databases were designed for normalization and joining of tables.  Don't make the mistake that far too many software developers have of jumping to optimization conclusions that lead them down a path prior to doing any actual tuning or profiling.  You don't know what your bottlenecks will be until you have a working application.  Don't assume that joining tables together will perform poorly.