Should I split this large table into three smaller tables?

Question

I'm designing a PostgreSQL database that will contain information about photos uploaded by my users. All users will have at least one main photo and optionally one or more public and/or private photos. My first schema is as follows:

user
----
id (PK)

photo
-----
id (PK)
user_id   (FK to user)
photo_id  (unique identifier such as "aK1q9")
type      ("main" or "other")
access    ("public" or "private")

The query that will get run the most is:

SELECT p.photo_id FROM photo p INNER JOIN user u ON p.user_id = u.id WHERE p.type = 'main' AND u.id = (some user id);

The next most popular query will be:

SELECT p.photo_id FROM photo p INNER JOIN user u ON p.user_id = u.id WHERE p.type = 'other' AND p.access = 'public' AND u.id = (some user id);

The problem I forsee is that the Photo table will become extremely large over time as it will contain all public and private photos uploaded by all users. Since my most popular query will only be looking for the main photo IDs, would it make more sense to break my photo table up into three tables?

main_photo
----------
id (PK)
user_id   (FK to user but in a one-to-one relationship to user)
photo_id

other_public_photo
------------------
id (PK)
user_id   (FK to user)
photo_id

other_private_photo
-------------------
id (PK)
user_id   (FK to user)
photo_id

I would think this latter schema would be preferable because 1) each photo's type and access information is made explicit by where it's stored thus eliminating additional ANDs in my queries; and 2) my queries would run faster since they would be run against one of the three smaller tables rather than one huge table. Which is the optimal approach from a performance standpoint?

Thanks.

Answer 1

I think, this is a design question rather database technology. So, I provided the answer with what DB technology am current working on. But, you prefer to have it explained using pseudo, so here you go with another answer. I could go and edit the other one, am intentionally leaving as it is so that if SQL Server guy visits this page then he may appreciate it, we never know!

Table Name: User
Reason: Requirement is to always associate the primary photo with the user it make sense to store just the reference of Primary Photo Id along with User record.

Columns
----------------
UserId (PK)
PrimaryPhotoId (FK to Photo table)

Table Name: Photo
Description: It would be lot easier to manage just single table. It is definitely possible to extend columns in this table in future just in-case if you want to store more information like tool-tip, Alt text, image description, etc.. The other tables doesn't get disturbed and requires absolutely no changes.

Columns
----------------
PhotoId (PK)

Table Name: UserPhoto
Description: This table provides you the flexibility to have one to many relationship. It also identifies the record whether every Photo is private or public, using the flag (IsPrivate column). If you prefer to restrict that every user shouldn't associate the same Photo twice then the proposed composite Unique Key make sense, otherwise you need to extend the unique key but the table design should remain the same.

Columns
----------------
UserPhotoId (PK, AutoIncrement)
UserId (FK to User table)       - Composite Unique Key
PhotoId (FK to Photo table)     - Composite Unique Key
IsPrivate (Bit Data type to store 0 or 1. 0 value represents for 'Public', 1 value represents for 'Private' photo)

Answer 2

@CoderAbsolute's answer gives you a good design for your tables. Since he or she did not go into detail about why this approach is better, I thought it was worth adding another answer.

First of all, design your table structure in accordance with how your data fits together. Don't try to smoosh different types of things into one table, don't add several tables for the same kind of records. Try to 'normalize' - if a table will have the same info repeated many times, then move that info into a different table, and link it from the first table using a foreign key. You should be aware of what first normal form, second normal form and third normal form are. Many real-world databases do not match these standards completely, but being aware of what you should aim for will help you make a much cleaner design.

I would say, don't worry about optimization until you've already got a correct design. First of all, you don't yet know how many entries you will have in your tables. Secondly, database engines are designed to make queries as fast as possible, even if you have a lot of entries. Don't second guess the developers of your database software.

When you've figured out that you really do have a bottleneck, you should look first at indexing. We think of a database table as being something like an array. In reality, it could be stored as a bunch of 'blobs', one for each row, which might all have different locations on a disk, not necessarily in order. (This is not a super accurate technical description of DB internals. But it should give you a clearer picture of how the pieces fit together.) How does the database find all the blobs? It has a somewhere, a list of 'pointers' which tell it where to find each blob. So typically, finding every single row of a table is an efficient process. It goes through the list and records each one.

Now, suppose that you most commonly retrieve all the photos for a given user. This might be a slow process, since it has to go through every single row of the Photo table, and look at the UserId field. In this case, you should add an Index on that field. An index is something like a lookup table, which allows the software to quickly find the location of all the rows with a given UserId. So it doesn't have to check every row in turn.

There are different kinds of indexes. Some are optimized for matching a particular value. This is probably the type you want for the UserId column. Some are optimized for finding things greater or smaller than some value. This might make sense for timestamps. (Give me all the photos from the last month.) You can have an index on multiple columns if this is what you regularly query on, or even on some function of one or more columns. Some indexes mean that similar items are stored close to each other on disk, to take advantage of caching to retrieve them more quickly.

You should familiarize yourself with the possibilities for your DBMS. Experimentation can also be very valuable here, since the exact speedups depend on your settings and also your hardware configuration.

Answer 3

In SQL Server 2012, I would design the schema something like this.

Primary Photo is tightly associated with User.

CREATE TABLE [dbo].[User](
        [UserId] [INT] IDENTITY(1,1) NOT NULL,
        [PrimaryPhotoId] [INT] NOT NULL,
     CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
    (
        [UserId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_Photo] FOREIGN KEY([PrimaryPhotoId])
    REFERENCES [dbo].[Photo] ([PhotoId])
    GO

    ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_Photo]
    GO

A generic table to hold photo information. In future, you may prefer to hold more information like tooltip, ALT text, description, etc and you don't need to touch the other two tables.

CREATE TABLE [dbo].[Photo](
        [PhotoId] [INT] IDENTITY(1,1) NOT NULL,
     CONSTRAINT [PK_Photo] PRIMARY KEY CLUSTERED 
    (
        [PhotoId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

A table which holds the optional photos based on Access identifier.

CREATE TABLE [dbo].[UserPhoto](
    [UserPhotoId] [INT] IDENTITY(1,1) NOT NULL,
    [UserId] [INT] NOT NULL,
    [PhotoId] [INT] NOT NULL,
    [IsPrivate] [BIT] NOT NULL,
 CONSTRAINT [PK_UserPhoto] PRIMARY KEY CLUSTERED 
(
    [UserPhotoId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[UserPhoto]  WITH CHECK ADD  CONSTRAINT [FK_UserPhoto_Photo] FOREIGN KEY([PhotoId])
REFERENCES [dbo].[Photo] ([PhotoId])
GO

ALTER TABLE [dbo].[UserPhoto] CHECK CONSTRAINT [FK_UserPhoto_Photo]
GO

Based on the above schema design, the following queries would be helpful!

--To get primary photo listing only
SELECT  Photo.*
FROM    [User]
        INNER JOIN Photo ON [User].PrimaryPhotoId = Photo.PhotoId;

--To get optional photo listing (just for PRIVATE)
SELECT  *
FROM    UserPhoto
        INNER JOIN Photo ON UserPhoto.PhotoId = Photo.PhotoId
                            AND UserPhoto.IsPrivate = 1

--To get optional photo listing (just for PUBLIC)
SELECT  *
FROM    UserPhoto
        INNER JOIN Photo ON UserPhoto.PhotoId = Photo.PhotoId
                            AND UserPhoto.IsPrivate = 0

--To get optional photo listing
SELECT  *
FROM    UserPhoto
        INNER JOIN Photo ON UserPhoto.PhotoId = Photo.PhotoId

I think, it is OK to maintain the photos in a single table and have it referenced to other tables. If it grows extremely large then think about Partitioning concept. I haven't considered about indexes which can go on UserPhoto table.