-3

Wondering the best way to architect a scrapbook application that has different content types/templates: Text, Video, Image, Gif etc. A page can contain any number of content types ex:

A Collage Template can have 3 images, a video, and some text on it.

Do I have a separate table for each type? How does the page know what table to look up? Does it have to scan each table?

Here's what I have but not sure how to piece it together. Seems like a lot of tables and a lot of lookups and redundancy, so do I have to lookup each table for items that match page_id? Would a join do this in one round trip? Would a jsonb column make sense? If I had say 20 different content types, this would be a lot to keep track of. Is there a more generic/universal way of doing it?

Thanks!

Table: pages

id created_at created_by page_type hashtags Table: text

id text font dimensions coordinates color opacity rotation url Table: video

id page_id video_url dimensions coordinates opacity rotation duration transcription url Table: image

id page_id image_url dimensions coordinates opacity rotation -url Table: gif -id -page_id -gif_url

dimensions coordinates opacity rotation url Table: audio -id -page_id -audio_url -duration -transcription -url

**Updated with more fields and changed from messaging to scrapbook for better explanation

user433575
  • 357
  • 1
  • 4
  • 13
  • This still has all the problems from feedback here & on your deleted [repost](https://stackoverflow.com/q/72587729/3404097) (especially, multiple questions) & the overall topic is still a duplicate of the Q&A this is closed as a duplicate of & many others. PS Please don't insert "EDIT"s/"UPDATE"s, just make your post the best presentation as of edit time. Please read the edit help re block & inline formatting for code & quotes & re line breaks. PS "[best way](https://meta.stackexchange.com/q/204461)" & "not sure" & "Seems like" & "a lot" don't mean anything in particular & are not helpful. – philipxy Jun 13 '22 at 02:08

1 Answers1

1

Obviously your app has to know how to handle each of the different content type (Audio would be different from Image. etc) but from the database they are pretty much the same. A url is essentially a string, you may need to escape some characters but it is still just a string. So you could just have 1 table. However I would probably have 2: message and message_contents.

create table messages( msg_id      integer generated always as identity
                                   primary key 
                      , created_at timestamp with time zone
                      , created_by text
                      ) 

create table message_details( mdtl_id      integer generated always as identity
                                           primary key 
                            , msg_id       integer 
                                           references messages(msg_id) 
                            , message_type text  not null   
                            , val_or_url   text  not null
                            );

I moved message_type out of message and detail. This permits a message to have multiply types. I.e. Image and Text for the same message. For better integrity create a message_types table and the the column message_type becomes a foreign key to the that table.
Essentially your desired generic/universal way boils down to just create 1 table to hold all types.

Belayer
  • 13,578
  • 2
  • 11
  • 22