Hi everyone, I need a bit of help. I have a couple tables ('characters', 'locations' etc) that store documents. I'm trying to create a timeline that allows people to add instances of a document to the timeline. For this I figured I'd create a 'document_instances' table to join an instance in a timeline to the document itself. The issue is that because I have multiple tables, I end up needing to construct a join based on the table name in a column. So you can see that document_id is the row I need to join and document_type is the table that the row resides in. Now I've heard this is a bad idea, but I don't really know how else to construct this functionality without this document_instance table.

Are you doing this so that you can track the history or changes to a document? If so, I do not understand why you need multiple tables. Why not have just one table like this: updated_at (Date of the change in doc), doc_id, user_id, doc_type, doc_contents, etc. This way you can add a new row if a doc is created, a doc is updated, or deleted. And when you query all rows with one particular doc_id and order it by the date, you would get a history of all changes to it from creation to deletion. This obviously assumes that that is what you are trying to do. I could be misunderstanding this.

It's a journal that has different types of documents, like a character document, location document etc. And I'm constructing a timeline document that allows you to put documents into an ordered timeline (ordered by the user, not by date). Documents might be referenced in multiple timeline documents, or multiple times in the same document.

Regardless of how you are ordering the documents, both methods are valid but I personally prefer one where all documents are in one table. Unless there is an explicit purpose for it, there is no need to have multiple tables for each type of document.

I'm leaning towards that as well, I'll have to migrate data over

You should look into "Postgres Views". They are essentially a way to use complex queries again and again. So, for instance, if you had a VERY complex query with several joins, you would write it once and then create a "view" for it. Lets say you call that view "complex_query". Once that is done, you would only have to do this:

SELECT * FROM complext_query;

any time you want to run that complex query. This is only a convenience thing that you might find easy to use. As for the problem about duplicates, it is likely that you are using the wrong kind of

JOIN

. There are five or six kinds of joins in total i think.

Thanks for the tip about postgres views

Hmm, the link doesn't work for me

I've tried inner joins, full outer joins, and left joins

How about this

that works

no worries

Hey just wanted to let you know I solved the issue (i think). My problem was that a join was the wrong idea because I don't have a related/common column. Using a union gets me the right result, although it's a pita to specify each column manually.

I think you dont have a choice but to specify each column like this

Thats interesting, I imagine for large datasets with many columns and queries it could get unmanageable

I am not a pro in this myself. I started using Supabase/postgres only a month or so back and as I am in the development stage, my schema keeps changing depending on what I need to do and what works for me best. Now I was in a similar position a week or so back when I couldnt decide between having two tables and joining them OR having one bigger table. As I was and still am a noob, I decided to go with the EASY TO IMPLEMENT approach both in Supabase as well as in my front end. if you are in the dev stage, then you do not need to have a solid schema right from the start. Once you develop and work on it, you will find and end up on the sweet spot that works best for you. Try this UNION method and then try a single and bigger table. See what works best for you.

Thanks for the help 🙂