I am making an article website. I have a database called CATEGORIES
with tables: Articles
, Entertainment
, Lifestyle
. Articles
contains all the entries of both Entertainment
and Lifestyle
. Entertainment
contains all entries of Entertainment type and Lifestyle
contains all entries of Lifestyle type. Each table has 7 columns: id, category (category of the article), link (link of the article), title (title of the article), image (image URL of the article), Counter (the number of views of the article), dateStamp (the date in which the article was written).
Would it be more efficient to get rid of the Lifestyle
and Entertainment
tables, and just rely on the Articles
table? The reason why I separated them this way is because when the user is reading let's say an Entertainment article, a random list of Entertainment articles are shown. To show random articles I take the highest id in the Entertainment table, get a random number between 6 and the highest id (6 is the number of articles I want to display), and then let's say I get 8 off of the random generator, then articles 8 though 3 are displayed (DESC LIMIT 6
). Here's the code:
<?php
$MAX_ID = $db->query("SELECT MAX(id) FROM Entertainment");
$MAX_ID = $MAX_ID->fetch_array();
$MAX_ID = $MAX_ID[0];
$RAND_NUM = mt_rand(6, $MAX_ID);
$resultSet = $db->query("SELECT * FROM Entertainment
WHERE id <= $RAND_NUM ORDER BY id DESC LIMIT 6");
if ($resultSet->num_rows != 0) {
$conditional = true;
$conditional2 = true;
echo "<div class='row'>";
while ($rows = $resultSet->fetch_assoc()) {
$image = $rows["image"];
$title = $rows["title"];
$link = $rows["link"];
$count = number_format($rows["Counter"]);
if ($conditional == true && $conditional2 == true) {
// display articles
} else {
// display other articles
}
}
}
If I removed the Entertainment
table, then this query would not work, and I don't know how else I would make it. The final question is would it be more efficient (performance wise) to separate the tables into different categories or combine the tables into one table?