CODEWITHSUNDEEP
pythondjangopostgresql
Donato Perconti
Donato Perconti

Reputation: 813

Django Models - Many to many persistent order

I'm looking for a way to have a Many-to-many relationship between objects while still persisting order. An example use case for this is a signup sheet. A user can sign up for many events, but I need to know who signed up first, second, third, etc...

Is there a straight forward way to doing this?

Upvotes: 1

Views: 103

Answers (1)

joanolo
joanolo

Reputation: 6328

You have two easy ways of doing such a thing:

  1. Use a timestamp, as already suggested by Moses Koledoye.
  2. Use any SEQUENCE number (or a SERIAL column).

For instance, let's assume these are your users and events tables:

CREATE TABLE users
(
    user_id INTEGER /* or SERIAL */ PRIMARY KEY,
    user_name text
) ;

CREATE TABLE events
(
    event_id INTEGER /* or SERIAL */ PRIMARY KEY,
    event_name text
) ;

You could define the many-to-many connection table in two different ways:

CREATE TABLE users_x_events
(
    user_id INTEGER REFERENCES users (user_id),
    event_id INTEGER REFERENCES events (event_id),
    created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id, event_id)
) ;

CREATE TABLE users_x_events_v2
(
    user_id INTEGER REFERENCES users (user_id),
    event_id INTEGER REFERENCES events (event_id),
    serial_id SERIAL,
    PRIMARY KEY (user_id, event_id)
) ;

I do prefer to have a timestamp than a serial, because it gives a piece of information that is normally useful. You often want to know when things happened (and who made them).

If you want extra security, you can have an ON BEFORE INSERT OR UPDATE trigger(s) to prevent any change to the created_at (or serial_id) column.

Upvotes: 1

Related Questions