Create unique sequence per set of rows sharing the same foreign key value

Question

In my system, I want each user to have an own id sequence for his articles.

So, I have created a database that has a table that depicts a correspondence between users and their articles. Here is the outline of such table:

 +------+----------+---------+
 | id | article | user_id |
 +------+----------+---------+
 | 1 |some notes| 1 |
 +------+----------+---------+
 | 2 |some notes| 1 |
 +------+----------+---------+
 | 3 |some notes| 2 |
 +------+----------+---------+ 
 | 4 |some notes| 2 |
 +------+----------+---------+
 | 5 |some notes| 3 |
 +------+----------+---------+

But I want to generate the ids in this table in the following way:

 +------+----------+---------+
 | id | article | user_id |
 +------+----------+---------+
 | 1 |some notes| 1 |
 +------+----------+---------+
 | 2 |some notes| 1 |
 +------+----------+---------+
 | 1 |some notes| 2 | 
 +------+----------+---------+
 | 2 |some notes| 2 |
 +------+----------+---------+
 | 3 |some notes| 2 |
 +------+----------+---------+
 | 1 |some notes| 3 |
 +------+----------+---------+

So, when a new user is being created in the associated user table, an id sequence for his articles will start from 1.

Is there any generally accepted approach to solve this problem?

Answer 1

This has been asked many times. The long and the short of it: Don't try it. You'd run into all kinds of problems with concurrency, gaps introduced by deleting rows, etc. Make id a plain serial column and create a VIEW to display a running number per user_id.

CREATE VIEW v_user_article AS
SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY id) AS per_user_id
FROM user_article;

A column article with 'some notes' hardly makes sense for your setup. And ordering by it makes even less sense, since the numbers would change with every new article that does not happen to sort last. To get a stable sort order and stable numbers (except for deletes!) use the serial column to determine the sort order. See:

• Serial numbers per group of rows for compound key

• Khaja Minhajuddin: Wouldn't this reassign the per_user_id whenever there is a deletion? That may be a bit of a problem if this is being used for cases where people refer to things by id. For instance, if you have an issue tracking system and you want a different series of ids for each project, this would be a problem. Do you have any advise on how to handle those?
• Erwin Brandstetter: @KhajaMinhajuddin: Gapless numbering starting with 1 is for the human eye. For all other purposes operate with the immutable serial ID generated by a sequence and be prepared for gaps.
• sigpwned: This view could be a good candidate for a materialized view. Also, you could avoid gaps due to deletes by using a tombstone (e.g., a delete flag) in lieu of actual deletes.

Answer 2

This sounds like you need to use a windowing function. something like:

SELECT user_id, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY article)
FROM [table]

Just an additional note, you'd have to make sure you the sequencing is static and you get he same ordering based on the ORDER BY clause in the windowing function.

Answer 3

Good that you went with the view row_number() solution

If you were stuck on hard coding
I know this works in MSSQL but have not tested with postgresql

insert into table (article, user_id, id) 
select @article, @user_id, isnull(max(id), 0)+1 as [id] 
from table where user_id = @user_id