Using a key value pair list/array in postgres json

Question

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.

Gordon Linoff · Accepted Answer · 2015-08-06 02:08:06Z

1

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,
    . . .
);

answered Aug 6, 2015 at 2:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rorschach Over a year ago

Thanks for the advice, I wouldn't have thought to just make a whole new table. (I will accept this in one day if it remains the best answer)

Collectives™ on Stack Overflow

Using a key value pair list/array in postgres json

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related