Jump to content
  • 0

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


Prinapo

Question

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 (ANYand 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

 

 

 

 

Link to comment
Share on other sites

1 answer to this question

Recommended Posts

  • 0

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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...