The question:
So i need to build at database where the admin user can add and remove models without me.
Instead of the normal type with all the values in one big table and them all with varchar type, I was thinking of putting each field in a model in a separate table with EntityID and Value and whatever that field requires.
So a Model example of a person could be:
| FieldName | Type |
|---|---|
| Name | varchar |
| Birthday | date |
| Address | int (Linked to a model) |
The database could then contain 3 tables:
- One With EntityId and varchar to store the names.
- One with EntityID and Date to store the date
- One with EntityId and EntityID to point to the address model
The selects would do a lot of joins but we could index the Fields that need searcing, EntityId would be a int and always be indexed.
The asp.net server is gonna keep control on the models and build querys that only selects what they needs.
Do you see any major problems with this, i know we don’t like EAV and we are locked to sql server for now.
I am not a DBA man…
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Method 1
All implementations of EAV, even if you have the kind of “metadata table” you are describing, result in the Inner Platform Effect:
The inner-platform effect is the tendency of software architects to create a system so customizable as to become a replica, and often a poor replica, of the software development platform they are using.
It’s up to you to write code in your client layer to implement enforcement of basic things like data types and simple constraints.
For example, you are right that you can separate the varchar, date, and integer attributes into three tables. Alternatively, you could create a single attribute table with three columns of varchar, date, and integer, and store your value in the column for the correct data type of a given attribute, while the other two would be NULL.
But either way, how does your client application know which table or which column to use? You would have to write client code to consult your metadata table per field, and build the SQL queries through conditional logic. Every time. No matter how simple the query would be in a normal database, it now becomes a major effort, and you will end up writing screens and screens of query-template-builder code to try to make it easier.
Then you may have some attributes that are mandatory for a given model. In normal table design you’d just designate a column as NOT NULL. But here, you’ll have to add more columns to your metadata table to define an attribute as mandatory.
Then you might also consider whether each attribute can have a DEFAULT. Or character sets and collation. Or different types like DECIMAL.
Think about how you would model a UNIQUE constraint in an EAV table. Or a FOREIGN KEY constraint.
Eventually you will implement a database, within a database. That’s the Inner-Platform Effect.
It can be done, but it’s a lot of work, and you will be responsible for the inevitable bugs. Full relational databases take dozens if not hundreds of engineer-years to implement, and they still have bugs.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0