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?
3
Answers
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 toDocumentMetadata
and containsDocId
,status
,docType
etc.CarDocument
andEmployeeDocument
tables contain columns that are specific to each type such ascarMake
andage
.Can bind the tables via Foreign Key from
DocumentMetadata
table to document-specific tablesIt’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)
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:
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).
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.