I am trying to use Base to make a database to catalog a collection of books, audio, and video files. My database currently has 4 tables:
Tables: Books, Audio, Video, Index
The Index table has the fields “Global_ID”, “Title”, and “Category”, with the Global_ID being the primary key. This table is meant to hold a list of everything (books, audio, and video titles, as well as a unique identifier for every record). The Category field is a text field that says what type of item the record is (book, audio, video).
The Books, Audio, and Video tables each have 2 fields, (BookID, Global_ID)(AudioID, Global_ID)(VideoID, Global_ID).
How can I relate these tables such that each item has its own primary key (ID) within its own table, but all items have a unique Global_ID that is unique across all tables?
Would my data entry to add new items be done in a form based on the Index table? And How would BookID, AudioID, and VideoID be automatically generated in the corresponding tables when I create records in the Index table?
I have tried opening up the relationships window and creating linking each tables primary key to the Global_ID in the index table, as well as trying to make BookID, AudioID, and VideoID fields in the Index table and linking each tables primary key to the similarly-named field in the Index table.