Schema of Data

[iamstiil]

In order to reduce reworking of the views, I would suggest talking through the data we want to save/provide for the book. This will help us design the frontend with the data in mind.

From what I have read so far we need something similar to this:

interface Book {
  title: string;
  author: string | number;
  status: 'available' | 'rented';
  location: string;
}

Let's discuss :D

[pawel-cebula]

Good idea, thanks for making the suggestions.

Can you explain the rationale for Book.author accepting both string and number as a type? Is that because we will want to store i.e. a name and an id for each author?

Perhaps we could specify Author as another type:

interface Author {
  name: string;
  id: number;
}

And then modify Book accordingly:

interface Book {
  title: string;
  author: Author;
  status: 'available' | 'rented';
  location: string;
}

[iamstiil]

I was thinking that we might not need to save the author as a separate entity since the main goal of the application is just managing the books. So in that case we might be fine just saving the name, hence string, but if we decide to save it as a separate entity than an id will be enough, hence number(assuming we use numbers as keys).

[TejasQ]

Since we're planning to use Hasura for the backend (see https://github.com/TejasQ/bookoid/issues/3), the schema is quite predictable. Here's what I suggest: we can get started modeling the data already in Hasura Cloud and go from there. I've already created a rough schema. Feel free to try this in the GraphQL playground. Password is eklwuaqldxfutaokaddakgunmnnblklt for now.

Feel free to add/delete/update the data model with tables and relations as you feel comfortable.

Demo query is:

{
  authors_books {
    book {
      title
      description
    }
    author {
      family_name
    }
  }
}

Feel free to reach out to me if you need any pointers around this.

[pawel-cebula]

@TejasQ I've finally managed to catch up on GraphQL and Hasura basics and would be keen to work on the schema. I've tried accessing the GraphQL Playground on Hasura that you've shared but the secret key is not working for me. Could you reshare it or grant me user access via my Hasura account? It should be tied to my email (ptcebula@gmail.com) or github name (pawel-123).

[pawel-cebula]

@TejasQ In the meantime I've set up another project on Hasura and implemented a schema, using yours as a starting point (I can later transfer the agreed changes in the original project as well):

URL: https://equal-squid-78.hasura.app/v1/graphql key: 98368d15-04c7-4e31-946c-c3f73d94807a

A rough summary of the current schema:

author(id, given_name, family_name, bio)

• one author can have many books

  book(id, title, description, isbn, status, author_id)

• one book can have one author
• book has a status, which is an enum field from book_status table with two rows: stock and lent
• book has isbn (background info) instead of bar_code (though we might need to split it into ISBN-10 and ISBN-13 - it could be interesting to later implement programmatically the conversion between these numbers, as I assume some books will only have one of them and the missing one could be populated automatically)

  user(id, username, email, role)

• user has a role, which is an enum field from user_role table with two rows: user and admin
• one user can have one role, while one role can have many users

I've implemented the relationships accordingly but it would be great if you could double-check them.

The following query should give us all the data required for list/grid views from #2

{
  book {
    id
    title
    author {
      id
      family_name
      given_name
    }
    status
  }
}

A couple of questions:

1. I feel that author<>book should be a many-to-many relationship, as one book can have many authors. I've thought about implementing it using a bridge table approach recommended here but I wanted to check first if you think it's the right approach?
2. One detail that is missing in the schema now is specifying which user has a book when its book_status is lent. Since book_status is an enum field / table, we can't add another field there (i.e. user_id) to tie it to a user. Maybe we could create a nullable field in book table, i.e. book_user_id with a foreign relationship to user_id field of user table (only allowed when book_status is lent)?

[TejasQ]

@pawel-123! Great to hear from you! You've done incredible!

To answer your questions,

I feel that author<>book should be a many-to-many relationship, as one book can have many authors. I've thought about implementing it using a bridge table approach recommended here but I wanted to check first if you think it's the right approach?

It is a great approach. Make it so!

One detail that is missing in the schema now is specifying which user has a book when its book_status is lent. Since book_status is an enum field / table, we can't add another field there (i.e. user_id) to tie it to a user. Maybe we could create a nullable field in book table, i.e. book_user_id with a foreign relationship to user_id field of user table (only allowed when book_status is lent)?

This is a great question. Having a column on books that references a single user seems to be the simplest approach. One book can always be owned by one user. If we have multiple copies of the same book, then that becomes a duplicate row of data in books. Seems simple enough for now. We can always iterate later. Let's KISS and move forward!

[pawel-cebula]

@TejasQ Thanks for the quick feedback! I've implemented the following changes as per your recommendations:

book_author(book_id, author_id)

• a new bridge table for many-to-many relationship between books and authors
• (book_id, author_id) as a composite PK

book(title, description, isbn, status, owner_id, borrower_id)

• a new field owner_id, each book must have one owner (with user_id as FK)
• a new field borrower_id, each book can have one borrower (nullable field, with user_id as FK)

Could you check if they are implemented correctly?

When I was adding books via Hasura GUI, there was no field(s) for authors and I had to add book_author pairs manually via that table. Is that normal for bridge tables or did I maybe mess up some relationships?

For reference here's a screenshot of a sample query that should have all relevant data points for list/grid view in #2:

And the query:

{
  book {
    title
    isbn
    status
    book_owner {
      username
    }
    book_borrower {
      username
    }
    book_authors {
      author {
        family_name
        given_name
      }
    }
  }
}

[TejasQ]

When I was adding books via Hasura GUI, there was no field(s) for authors and I had to add book_author pairs manually via that table. Is that normal for bridge tables or did I maybe mess up some relationships?

This is normal.

You've done exceptionally great work here! Shall we plug it into #6 and see how it goes?

[TejasQ]

It's here btw https://bookoid.netlify.app/

[pawel-cebula]

Thanks! In the next couple of days, I'll get more familiar with the existing React/TS code base and read up on the potential options for GraphQL client-side integration and share my views.

[TejasQ]

Great! In the meantime, would you mind if someone else handled the Frontend? Your backend set up is amazing and I am really eager to see something interact with it ASAP.

[pawel-cebula]

Sure, makes sense!

[TejasQ]

@pawel-123 – seems your instance is down. Could you help @iamstiil? https://github.com/TejasQ/bookoid/issues/10

[pawel-cebula]

@TejasQ @iamstiil Thanks for spotting this. I had a look and unfortunately couldn't find the issue so far. I assumed it might be due to automated DATABASE_URL maintenance on Heroku, which updates the database URL (last time on 19.11). However, I double-checked on Hasura and I did have the Heroku database URL sync enabled there as per the default setup instructions.

Can you double-check if it still seems to be down and let me know how you observed this? I can then look into this again.

Alternatively, feel free to have a look into the console, perhaps I'm missing something!

URL: https://equal-squid-78.hasura.app/v1/graphql key: 98368d15-04c7-4e31-946c-c3f73d94807a