SQL table design: table with multiple one-to-one relationships

Question

In SQL I have a table setup

RegisterTable
----
DocId int
status int
docType int

CarDocument Table
----
carDocId int (PK, FK -> RegisterTable)
name string
carMake varchar

EmployeeDocument
----
emplyeeDocId int (PK, FK -> RegisterTable)
name varchar
age int

This is a database about documents. Tables design have no relevance to the question. So I have different documents Cars/Emplyees/etc... -- they all have completely different set of fields, unrelated.

I need to have metadata for these documents, which is represented in RegisterTable. This metadata is similar across documents. So it's a bit like inheritance.

Which is the DB design for this case? Currently I made three separate tables and created one-to-one relation from CarDocument/EmployeeDpcument to RegisterTable.

When I create a document, I first create it's metadata in RegisterTable, then I take the key and use it to create a document in corresponding CarDocument or EmployeeDocument table.

This works but looks cumbersome to me.

Extra info: I have 10-20 different document tables. I use typeorm as my ORM solution.

Research: Has similarities with Table has one to one relationship with many tables

My design works but RegisterTable is kinda fake since it holds all the docIds. Which is the best DB design for this case?

Answer 1

Postgres actually does inheritance - see https://www.postgresql.org/docs/current/tutorial-inheritance.html

Aside from that, if you have metadata that is always the same across various types of documents, your approach to have a metadata table with a relation to the document tables is the right one, in principle (see below).

The metadata table itself does not need to know about the tables that reference it. Your query logic can derive the correct secondary document from the docType and the docId.

For your specific case, as you've posted it above, if a single "status" field is the only actual metadata you hold in that table, I think you would be better off to simply add that field to the document tables. Only if you have a fixed set of metadata that you don't want to replicate over many different tables does it make sense to split it into its own table.

Answer 2

There's a more flexible and scalable approach that can be used.

A single table would store all document metadata and then another separate table for each document type that stores specific details for that type of doc.

RegisterTable can be renamed to DocumentMetadata and contains DocId, status, docType etc.

CarDocument and EmployeeDocument tables contain columns that are specific to each type such as carMake and age.

Can bind the tables via Foreign Key from DocumentMetadata table to document-specific tables

It's not only more flexible because you can keep adding new types of docs, but also avoids creation of a meaningless table that doesn't have any real info (RegisterTable)

Answer 3

I see nothing wrong with your design. One key point, anyway, is deciding if you'll share the IDs for all your entities/tables (as you're doing) or have separate IDs. The second choice may be the more tidy and flexible. You'll have something like this:

RegisterTable
----
docId int
status int
docType int

CarDocument
----
carDocId int (PK)
docId int (FK-> RegisterTable)
name string
carMake varchar

EmployeeDocument
----
emplyeeDocId int (PK)
docId int (FK-> RegisterTable)
name varchar
age int

Of course, you can also have just ONE big table with a lot of fields, filling each field (or not) depending on the docType, and maybe with different semantics for each different docType (no, I'm joking, don't do that).