PostgreSQL – Unique ID for Row of a Set in One Table

postgresqlprimary-keyunique-constraint

I know the title is a little weird but what I actually want to do is simple. In my Postgres db a user creates a profile and uploads an image.

user_id: 1, photo_id: 1

Now a second user makes a profile and uploads two images.

user_id: 2, photo_id: 1, photo_id: 2

In my photos table, how can a create a unique identifier out of the combination of user_id and photo_id? Neither are unique in the table but their combination should be.

Best Answer

First, photo_id should be unique on the table. Ignoring sound practice however, you can still do this with a composite PRIMARY KEY

CREATE TABLE photos (
  user_id  int,
  photo_id int,
  PRIMARY KEY (user_id, photo_id)
);

INSERT INTO photos (user_id,photo_id) VALUES (1,1),(1,2);

--duplicate already exists
INSERT INTO photos (user_id,photo_id) VALUES (1,1);

Composite natural PRIMARY KEY's are not bad, however I'm not a fan of having them on multiple surrogate keys. If you're going to create an ID, may as well make it unique.

Related Solutions

Correct data model for users with clients

You can add a unique index across the user_id and username in the Clients table this will ensure that the username can only occur once per user_id. Though you should be aware that if the user_id column is nullable it will also apply to clients that do not have a user_id assigned to them.

EDIT

Using a composite primary key that consists of the client_id, user_id and the username will not meet your requirements. This is because the addition of the client_id makes the grouping unique. Take the following rows as an example:

client_id user_id username
1         1       John Doe
2         1       John Doe

If you had a composite primary key of all 3 columns the above would be allowed - however, if you just added a unique index across the last 2 columns the above would be invalid - which is your intended result.

You could add a composite primary key across the last two columns but this is not a good design as it adds additional complexity when attempting to reference the client. A better design would be to keep the client_id as the primary and add a unique index across the last 2 columns.

Single Table vs Multiple Table Design for Distinct Records Queried Together in PostgreSQL

I won't put all of them together. Even if you query them together, it may occur that your application will be able to post multiple images per post or multiple events per post or multiple posts per event.

In this case you would be able to save space. The JSON may be a good idea, but it would be better (in view of indices) to use multiple tables. This way you can provide indices which may improve the query process.

Another idea could be to use permutation. Which means you have a global table which holds many different types with the same structure. For example:

CREATE TABLE types(
    id int,
    type nvarchar(50),
    property1 int,
    property2 nvarchar(50)
)

This way you can store a value with Type = 'Image' which holds a property1 (order number) and a property2 (a path). And additionally you can hold an event like Type = 'Event' which holds the date in property1 and the name in property2. Just as an example.

Best Answer

Related Solutions

Correct data model for users with clients

Single Table vs Multiple Table Design for Distinct Records Queried Together in PostgreSQL

Related Question