Jump to content
#StayAtHome ×
mactron

MySQL architecture

Recommended Posts

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 by mactron

Share this post


Link to post
Share on other sites

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.

  • Thanks 1

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 by mactron

Share this post


Link to post
Share on other sites

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

  • Thanks 1

Share this post


Link to post
Share on other sites

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 by mactron

Share this post


Link to post
Share on other sites

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 by chhorn
  • Thanks 1

Share this post


Link to post
Share on other sites

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.  

  • Thanks 1

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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

Edited by Barand

Share this post


Link to post
Share on other sites

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

Edited by mactron

Share this post


Link to post
Share on other sites

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

Perhaps combine like/dislikes with a flag column to distinguish

Edited by Barand
  • Thanks 1

Share this post


Link to post
Share on other sites

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.

  • Thanks 1

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.