Mysql sub-cat and parent cat with posts [Database schema]

0
votes

Okay so I have a simple category table and a separate posts table easy right but when the user posts a post I wast think should I store both the sub and parent cat in the posts table but would that not be a lot of data duplication so I instead just store the sub_cat then I use a few PHP functions to query the database for the primary cat and its name.

categories table

ID | cat_name | main_cat
1  | Dinner   | 0 
2  | Chicken  | 1

posts table

ID | title | sub_cat | fields that are not related to Q
1  | test  | 2       |

Get parent(main) category $sub_cat = is from a selection query that gets posts and their sub_cats

function main_cat($sub_cat){
    require("conn_posts.php");
    $stmt = $conn_posts->prepare("SELECT `main_cat` FROM `cats` WHERE `ID` = ?");
    $stmt->bind_param("s", $sub_cat);
    $stmt->execute();
    $stmt_results = $stmt->get_result(); // get result

    while($row_get = $stmt_results->fetch_assoc()){
        if($row_get['main_cat'] == 0){
            return $sub_cat;
        }elseif($row_get['main_cat'] !== ""){
            return $row_get['main_cat'];
        }
    }
}

This function gets any category name as long as the id is valid

function cat_name($cat_number){
    require("conn_posts.php");
    $stmt = $conn_posts->prepare("SELECT `cat_name` FROM `cats` WHERE `ID` = ?");
    $stmt->bind_param("s", $cat_number);
    $stmt->execute();
    $stmt_results = $stmt->get_result(); // get result
    $row_get = $stmt_results->fetch_assoc(); 

    if($stmt_results->num_rows <= 0){
        return 0;
    }elseif($stmt_results->num_rows == 1){
        return $row_get['cat_name'];
    }
}

My question is is this a good way to process my posts sub-category and parent category are there better ways of doing what I am currently doing? eg. is my database schema good(by good I mean is it better to just include the parent cat id in the posts table than to do the PHP server-side processing)?

phpmysql