0

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?

  • 1
    What do you need the listentries table for in the first place? It doesn't store any information at all. In Postgres you could model this with inheritance. Where petnames and peoplenames and lists inherit from the base table listentries – a_horse_with_no_name Nov 17 '15 at 22:15
  • 1
    Use table inheritance, aka subtype/supertype relationships – Neil McGuigan Nov 18 '15 at 0:04
  • Thank you both, I was not aware that MSSQL and PostgreSQL had inheritance capabilities. I'll research and see if that solves my problem. My initial scan of the topic suggests that it will. Thank you again. – Jeff Nov 18 '15 at 14:35
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.