Create tables and relation to store user specific detail for each row of the main table

[Prinapo]

Hi,

I'm trying to build a book collector app that, to simplify, is composed by two tables:

• BookTable
  • BookUniqueID
  • BookTitle
• UserTable
  • UserUniqueid (email)
  • Password

I need that any authenticated user can see the list of all the books together with the owning status (Yes or No)

To do that, I created a new table

• Owning
  • Owned (yes/no)
  • UserID
  • BookID

Then I created a View with the two tables:

• BookOwner
  • BookTable
    • BookUniqueID
    • BookTitle
  • Owning
    • BookID
    • UserID
    • Owned (yes/no)

Joined by the fields BookUniqueID (ANY) and BookID 

In this way I have a quite large table where each book is repeated for each user 

Then I create a DataPage referred to view BookOwner enabling the authentication filtering and filtering for  UserID=AuthenticatedUser

I see two problem in this approach:

1. the DataPage shows only the book owned by the user so that cannot be used to change the owning status on another book
2. the view will become very large with the growing number of users

So I have the impression that the way I organized the tables are not optimal and I need an help to understand how to build a better table system?

The other question is relating the View, it is possible to apply a filtering when the view is created insted of filtering once a DataPage is created?

If a template that perform a similar approach exist, please drive me to it 

Thank you for your help

Giovanni

 

 

 

 

[PotatoMato]

Hi @Prinapo,

For your first problem, it is possible restrict the data by user, so that the user can only see the records that are assigned to them: https://howto.caspio.com/authentications-and-connections/authentication/record-level-security/restrict-access-to-data-by-user-or-role/. Second, you can apply a filter when creating a View: https://howto.caspio.com/tables-and-views/what-are-views/creating-a-view-to-filter-data/

Hare are the best practices when creating a table: https://howto.caspio.com/tables-and-views/table-and-database-design/

Here are the ready made applications that you can check: https://www.caspio.com/apps/

 

-Potato