CODEWITHSUNDEEP
sqlsql-serverdatabase
user2216483
user2216483

Reputation:

SQL Server Table Design ; one table with type column vs multiple tables

I have made a website in which I have a blog and a products page. Posts and products are stored in different tables. I use microsoft sql server.

I want to create a table to store the views for each post and for each product. My 2 possible designs are:

  1. One table for all views

    (id, ref_id, date, ip, type)

    where type is either post or product

  2. Two separate tables, table post_views and table product_views

    post_views(id, post_id, date, ip)
product_views(id, product_id, date, ip)

Which design is better and why?

My reasoning:

Solution 1 requires more database space (we need to store the type value for each view), also requires a more "complex" query. We need to search by id and type.

Solution 1 is more compact. We have less tables, but the performance won't be so great. If we have 1 million records and 500k views are for the posts and 500k views are for the products, we would have to search all the views to filter by date (just an example) Pros of 1st solution are the compactness and that I use one less table.

The second solution requires one more table, but the query performance and disk space would be better.

This is a very common design question that I face and I would love to receive an answer from a very good expert.

Upvotes: 7

Views: 1788

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

Since posts and products each have their own tables, go with the second option - meaning different tables for post views and product views.

The reason it's a better option is that this option allows you to use foreign keys between the views tables and the posts and products tables and keep the posts and products separated.

the first option will also allow you to use foreign key between these tables, but it will mean that you can only post views where the ref_id exists in both posts and products tables. Also, it will force more cumbersome select statements to include different joins based on the type column.

Upvotes: 4

BobRodes
BobRodes

Reputation: 6165

With no more information than you have provided, I would go with the first option, simply because it is more concise. Unless you have a compelling reason to break them into two tables, you should keep them in one. It's easy enough to filter any queries by type as needed.

Another consideration is that it's more common practice in stored procs to have a variable field parameter (such as your type field) than it is to have a variable table name, so the function of any stored procs that you create will be a bit more obvious to those who come after you if you keep them in one table.

Upvotes: 2

Related Questions