Database Design: Multiple tables vs a single table

Question

I am making a website where there are different types of items such as blogs, posts, articles and so on. A user can set any one of them as his/her favorite. Now when I approach this thing, I have two options

1. Make a table for user favorites for each type of object.
2. Make a common table for all type of objects for all the users.

The problem with the 1st structure is that I will have to query a lot of tables for displaying the favorites of a particular user. But it will allow me to easily group the favorites into different categories.

However if I have to show all the favorites on one single page and merge them all, sorted according to time, then that becomes difficult. But if I use the second model, I can easily get the latest favorites, and also grouping them according to object type is not difficult, but I will have one large table site wide.

Which of the two strategies will be more scalable.

The 1st one entails multiple database queries, and the second one entails a large single table.

If it helps, I am using MySql

Solution 1

It seems that you already know the answer, but remember, keep the systems you design simple to modify as business models always change over time or they eventually fail (it's a generalization but you get the idea). A corollary of that is if you make a rigid model, fast or slow, it's rigid, changes will be harder and the end user won't see the difference, hence no money/happiness change is achieved, unless it's a very bad change. Your problem is not technical in a way a query works on the engine but more of a philosophical one, easy changes versus apparent speed. Ask yourself, what's the advantage of having a normalized database? Think about a clean architecture and design, performance is the least problem in todays world as processing is cheaper and storage also. But design is expensive. Normalization was made to make systems that don't depend on last moment decisions but on a structured design process. Big tables are not a big deal for MySql but they are a big deal to maintain, modify and expand. It's not just adding one more column, it's about the rigid structure of the data itself. Eventually in time you will just add columns that contain indexes, and those indexes will be pointing to small tables. MySql will be plowing it's way around all that data anyway. So i'll go for the first one, a lot of small tables, many-to-many.

Solution 2

I have this design on my website. My modules are: news, articles, videos, photos, downloads, reviews, quizzes, polls, etc etc. All in separate tables. I have a likes table where users can like or dislike a post (in your case favorites). The query to get these isn't that complicated.

First off for the most part MOST of my tables for the modules are structured the same way:

• id
• title
• content
• user_id (author)
• date
• etc

with a few exceptions being that sometimes title is called question or there is no content column. That does not cause any issues.

My likes tables is set up like this:

• id
• page_id
• module_id (what table did it come from...I have a modules table where each module has a title, associated id, directory, etc)
• post_id (corresponds to the module table id)
• user_id (user who did the liking or posting)
• status (0 = like, 1 = dislike)
• date (when the liking/disliking took place)

Modules table example:

• id
• title
• directory
• post_type

Example

id      title              directory         post_type
 1       News                news               news
 2     Episode Guide       episodes            episode
 3       Albums           discography/albums    album

Essentially yours would have a similar set up, modifying the table structure as necessary for your needs.

Query to get all the likes or favorites for a particular user:

$getlikes = mysql_query("SELECT DISTINCT post_id, module_id, page_id FROM likes WHERE user_id = $profile_id ORDER BY id DESC LIMIT $offset, $likes_limit", $conn);
$likes = mysql_num_rows($getlikes);

if($likes == "0"){
echo "<br><Center>$profile_username does not have any liked posts at this time.</center><BR>";
}
else {
echo "<table width='100%' cellspacing='0' cellpadding='5'>

<Tr><th>Post</th><th align='center'>Module</th><th align='center'>Page</th><tr>";

while ($rowlikes = mysql_fetch_assoc($getlikes)) {
   // echo data

$like_page_id = $rowlikes['page_id'];
$like_module_id = $rowlikes['module_id'];
$like_post_id = $rowlikes['post_id'];


// different modules have different fields for the "title", most are called title but quotes is called "content" and polls is called "questions"
if($like_module_id == "11"){
$field = "question";
}
elseif($like_module_id == "19"){
$field = "content";
}
else{
$field = "title";
}





// FUNCTIONS
PostURL($like_page_id, $like_module_id, $like_post_id);
ModTitle($like_module_id);
ModTable($like_module_id);
ModURL($like_page_id, $like_module_id);
fpgURL($like_page_id);


$getpostinfo = mysql_query("SELECT $field AS field FROM $mod_table WHERE id = $like_post_id", $conn);
$rowpostinfo = mysql_fetch_assoc($getpostinfo);
$like_post_title = $rowpostinfo['field'];

// Using my "tiny" function to shorten the title if the module is "Quotes"
if($like_module_id == "19"){
Tiny($like_post_title, "75");
$like_post_title = "\"$tiny\"";
}


if(!$like_post_title){
$like_post_title = "<i>Unknown</i>";
}
else {
$like_post_title = "<a href='$post_url'>$like_post_title</a>";
}

echo "<tr class='$altrow'>
<td>$like_post_title</td>
<td align='center'><a href='$mod_url'>$mod_title</a></td>
<td align='center'>$fpg_url</td>


</tr>";

$altrow = ($altrow == 'altrow')?'':'altrow';

} // end while

echo "<tr><Td align='center' colspan='3'>";

// FUNCTIONS - Pagination links
PaginationLinks("$cs_url/users/$profile_id", "likes");

echo "</td></tr></table>";

} // end else if no likes

Ok that may be hard for you to understand since I have alot of my own variables, but basically it gets the module id and post id from the likes table and then runs a query to get the title of the post and any other info I want like the original author.

I have "module" functions set up that will return the url or the title of the module given you provide an id for it.

Solution 3

So if I'm not mistaken, you are trying to create a Favorites table to collect the user's favorite items right? If so, you will need at least two tables.

Types: The types of the resources.

+----+---------+
| ID |  Name   |
+----+---------+
|  0 | blog    |
|  1 | post    |
|  2 | article |
|  3 | photo   |
|  4 | video   |
+----+---------+

Favorites: The most important part of the Favorite system, it's kinda like a relationships map.

+--------+----------+--------------+
| UserID | TargetID | TargetTypeID |
+--------+----------+--------------+
|    941 |        1 |            0 |
|      6 |      935 |            1 |
|     26 |       51 |            4 |
|      7 |       87 |            2 |
+--------+----------+--------------+

Posts: The example posts table, you might also have Blogs or Photos and Albums tables.

+-----+------------------+
| ID  |      Title       |
+-----+------------------+
|   0 | This is my post! |
|  51 | Oh, how are you? |
| 935 | Hello, world!    |
+-----+------------------+

Now, the SQL Query might be like this (untested):

-- Get the posts
SELECT p.*
FROM Posts p
LEFT JOIN Favorites f 
-- Which are favorited by the user 6
ON f.UserID = 6 
-- Also get the type id of the `post`,
-- so we can specify the favorite type of the favorite items
AND f.TargetTypeID = (
    SELECT ID 
    FROM Types
    WHERE Name = 'post'
)
-- Make sure we only get the posts which are favorited by the user.
WHERE p.ID = f.TargetID

With the SQL Query above, you can get the favorite posts which is been favorited by the User ID 6.

+-----+------------------+
| ID  |      Title       |
+-----+------------------+
| 935 | Hello, world!    |
+-----+------------------+

Comments

[Sachin]

Hey thanks alot for the exhaustive answer that you have given. I also have a structure quite similar to yours, so keeping it in the same table is not that difficult. Moreover I am working in Django framework of python which has a Generic Foreign Key. This allows me to keep different object references in the same table. But my question not operational is more of performance based. What would happen when the number of users would increase and these tables would start filling up?