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?
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).