CODEWITHSUNDEEP
jsonpostgresql
Rorschach
Rorschach

Reputation: 3812

Using a key value pair list/array in postgres json

I am making a database in postgres for the first time and am wondering about the best way to store something. I have users in one table and books in another table. How do I store the list of ID's of books that the user wants? Also, I want to also store a date for when that user has to have that book by. A book can be wanted by two separate people.

I was thinking of having a json column in the user's table that contained some sort of a list that stored the IDs of the book, but I also want to store the date concerning when the user needed the book.

Would "SETOF anyelement" be what I want for this? Would it be good for frequently "pushing" new books and "deleting" old books?

I have looked at: http://www.postgresql.org/docs/9.3/static/functions-json.html but it does not really supply the kind of higher level explanation that I am looking for.

Upvotes: 0

Views: 1032

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

A JSON column is (in all likelihood) a very bad idea. The right way in SQL is to use a junction table:

create table BookUsers (
    BookUserId serial primary key,
    BookId int references Books(BookId),
    UserId int references Users(UserId),
    TargetDate date,
    . . .
);

Upvotes: 1

Related Questions