I can use PostgreSQL or Microsoft SQL, whichever provides the needed capabilities.
I need help designing a database that has records that can reference FKs in different tables. Given the following fictitious structure:
CREATE TABLE Lists ( ListID int NOT NULL AUTO_INCREMENT, ListName varchar(50) NOT NULL, Description varchar(255), PRIMARY KEY (ListID) ) CREATE TABLE PeopleNames ( PersonNameID int NOT NULL AUTO_INCREMENT, GivenName varchar(50) NOT NULL, Surname varchar(50) NOT NULL, PRIMARY KEY (PersonNameID ) ) CREATE TABLE PetNames ( PetNameID int NOT NULL AUTO_INCREMENT, PetName varchar(50) NOT NULL, PRIMARY KEY (PetName) ) CREATE TABLE ListEntries ( ListEntryID int NOT NULL AUTO_INCREMENT, EntryID int NOT NULL, <-- this could be a PetNameID, PersonNameID, or even ListID, it's not a strictly enforced FK except by application code PRIMARY KEY (NameID) )
Ok, so in this example, the ListEntries table has a column called EntryID. The EntryID could reference the PersonNameID, PetNameID, or even another ListID field. CRUD functionality will be wrapped in an API which will enforce relations.
What's the best way to model this? Should I just create another column in the ListEntries table called
Table varchar(50) NOT NULL which references the table that the EntryID relates to?
I know I can use something like Redis or MongoDB, but fundamentally, the data is related, so shouldn't a Relational DB be used?
The database will NOT be updated frequently, but it will be read frequently. Also, could I generate a JSON file as a Materialized View and stuff that into another table for fast reads?