How to structure this database? (Knowledge collection app)

Hi! I am trying to build a knowledge collection app, where I save interesting bits of resources I find around the web.

I have an entity that I call “Knowledge Pack”.

There are currently 2 types of “Knowledge Packs” - “Video Knowledge Pack” and “Article Knowledge Pack”. The fields for each type are largely similar (they share many similar fields) but also have slightly different fields for each still:

“Video Knowledge Pack”:

  • Title
  • Description
  • URL
  • Summary
  • Video Creator
  • Number of Views
    -Video Thumbnail

“Article Knowledge Pack”:

  • Title
  • Description
  • URL
  • Author
  • Summary

I would like for my users to be able to browse “All Knowledge Packs”, filter by “Video Knowledge Packs” or “Article Knowledge Packs” that they created, and also have the flexibility to add different types of Knowledge Packs in the future - example “Podcast Knowledge Pack”.

Additionally, I also want to have a “Collections” function where users can group different Knowledge Packs together (example, someone can have a Sewing collection and inside the collection contain “Knowledge Packs” that can be video or articles according to their own selection.

Additionally, I also want to have a “Favorites” function where users can favorite different Knowledge Packs that they like.

Should I have separate databases for “Video Knowledge Pack” & “Article Knowledge Pack” or should I have a big database called “Knowledge Packs” and then have a selector field that selects “Video” or “Article” in this database?

If it is the former, how am I supposed to allow users to browse “All Knowledge Packs” if they come from 2 different tables? Also, how am I supposed to get the “Collections” function to work if we are trying to group things from 2 tables?

If it is the latter, wont it be weird that I have many blanks in the database (example, Article Knowledge Pack will not have any number of views) and can “article title” and “video title” both share the same “title” field for example?

1 Like

Hello @raelyn, What backend are you considering using (i.e., Xano)? What is your experience level in database architecture?

If you are new to designing databases, I would recommend Xano and suggest the following videos to get you started:

In these videos, you will find the basics to help you design what you have described. I would also suggest that you Google data normalization.

I hope this was helpful.

Sounds like a great idea, you might want to also look at building a Chrome extension, so that when you are on a web page you can quickly save it, part of it or a video directly from the page to your app. That will save you going back and forth between your app and the different sites you find.

In terms of DB structure…

Question: I assume one resource can NOT be in both types of pack?

There are quite a few columns here that are the same for both packs… so I think it makes sense to have a resource table that includes all these fields.

You could break out the packs into their own table if you think you might have additional ones in the future… audio book pack maybe? or premium packs? Then just reference the pack type in the resource table.

I am not sure you can go wrong with either approach.

Joining 2 tables is easy enough (and what DBs are good at), you can do that on the Xano side if you want or you can do some work in weWeb… but I recommend having Xano do most of the heavy lifting.

You can actually create an API endpoint that gives you joined data, you can do this with with ‘addons’ in the output tab in the API endpoint builder.

1 Like

Yes, I am using Xano! I have already watched all those videos (and more) but still don’t know whether I should create it in one big table or separate them into 2 tables :slight_smile:

1 Like

Hey @raelyn, I agree with @twinsticks, using the 3NF makes the most since because of the duplication of fields if you used two tables. Xano makes it easy to create separate “views” using different API endpoints. This method would then give you the effect of two different tables.

I like the idea of building a Chrome Extension too. Great idea @twinsticks!