I have some questions about database design.
I have a generic table structure used to store relationships.
Recently I refactored some things to use this generic structure instead of direct Fk columns, but now I'm not sure if that was really the best idea.
Original Schema:
+------------------+ +---------------------+ +----------------------+ | Book | | Note | | MetaParent | |------------------| |---------------------| |----------------------| | Id | | Id | | Id | | NoteId | | MetaParentId:(Null) | | MetaTableId | | +-------+ +----+ KeyValue | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | +------------------+ +---------------------+ +----------------------+
New Schema
+------------------+ +---------------------+ +----------------------+ | Book | | Note | | MetaParent | |------------------| |---------------------| |----------------------| | Id | | Id | | Id | | | | MetaParentId:(Null) | | MetaTableId | | + + +----+ KeyValue | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | +------------------+ +---------------------+ +----------------------+
So basically instead of having a direct Fk relationship between Book and Note we have an indirect relationship via the MetaParent table using the MetaTableId/KeyValue columns.
Currently the MetaParent table has about 500k records and things are running acceptably. But we do rebuild the indexes on it every night.
My concerns are that now the relationship between Book and Note isn't obvious. You have to know one exists and to use the MetaParent table.
Also performance, I'm not sure at what point we'd run into issues with joins against MetaTableId/KeyValue running too slow. It seems the more you add to this table the slower queries would get.
You should always enforce referential integrity by using "normal" FOREIGN KEYs.
In a nutshell, FOREIGN KEYs have the following advantages:
And here are the corresponding disadvantages of enforcing referential integrity in the application code:
Is there a name for this?
Not that I know of. I heard a term "generic FKs" being used, but that's probably not universal.
Is it good practice?
No (see above).
Any performance considerations?
Yes (see above).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With