Sql-server – Designing a database where records can reference different foreign keys

database-designpostgresqlsql server

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?

Best Answer

When you are trying to relate a table row to rows in different tables, depending on the circumstances, it is nearly always a case of type/subtype aka class/subclass aka generalization/specialization. The second of these terms is widely used in object modeling, while the third one is widely used in ER modeling.

In your case, the three subclasses would be PersonNames, PetNames, and Lists. I'm not sure what to call the superclass. I also wonder whether your fundamental entities are PersonNames or Persons, and ditto for Pets.

If you deside to model these items as a superclass/subclass case, then I'm going to recommend two design patterns or techniques to you. They are class table inheritance, and shared primary key. There are tags for these two techniques over in StackOverflow, and there is a tag for Subtypes here in the dba area. The questions tagged, and the info under the tags may help you to understand these techniques as they apply to your case.

Related Question