What is the proper name for this table schema?
We have a general database schema that we use for some of the tables in our system. The main reason is that we are running a database with multiple tenants, so not all of our users need the same fields. However, I don't know what the "correct" name is for this type of schema.
Here's an example of what one of our tables might look like:
ClientID | SurveyID | AnswerKey | AnswerVal ------------------------------------------- 1 | 1 | Fname | Fred 1 | 1 | Lname | Flintsone 1 | 1 | Email | Fred@flintstone.com 1 | 2 | Fname | Mickey 1 | 2 | Lname | Mouse 1 | 2 | Phone | 555-3343
We call them "Vertical Tables", but I don't know if this is correct.
source to share
"What's the name for this table schema?"
Shit.
Consider what you would need to do to put a type constraint on, say, the AnswerValue fields for an email.
Consider enforcement efforts of possibly required "completeness" constraints requiring that a particular set of fields be included in some surveys.
Consider the effort involved in creating individual rows (of which I assume it is possible to know ahead of time which columns are to be included because you know which user you are working for, so you know which fields they are interested in).
Consider the effort that goes into ensuring that no one is able to retrieve or manipulate any fields they are not interested in.
And I'm sure even more ...
source to share
I would call this the Soft-Coding database design . Sorry to be a little abrasive, but this kind of design just smells like an anti-pattern.
source to share