SQL Select Problem
in [Programming SQL]
|
From: Gert-Jan Strik on 22 Dec 2009 05:56 Frank Uray wrote: > My datamodel is normalized, the Key and the Value are > in different tables with relation and constraints. > I have simplified it just for testing. O boy. If I understand you correctly, you are saying that you have a table called Keys in which you store the names of the keys (such as "Profession") and a table called Values in which you store one or more values of this key for the Candidate (such as "Informatiker"). If that is the case, then your model is not normalized, and it is (as David noted) the EAV anti-pattern. You say you have relations and constraints. So tell me, how do you prevent someone from entering a "Title" with value "Informatiker" in the database? Or let's say you also have a key called "DateOfBirth". How do you prevent values like "2E3" and "1st of January 2 thousand" for this key? Or let's say that a Candidate should at least have a "Title" and a "Profession". How would you prevent Candidates with just a "Title", or just a "Profession"? What happens if someone changes the Key's name from "Profession" to "Education"? I already know your answer! Because with your current design, you cannot (easily) solve this in the database. So it must be solved in the application layer. All the unwanted situations that you allow in the database become a burdon for the developer who will then have to write complex queries to filter out any incomplete entities (in your case Candidates). The developer will have to code and manage the business rules, even the business rules about what constitutes a "Candicate". After a short while, this approach will likely fail. A developer will "forget" some of the rules, or someone will change data in the database directly (without using your application), etc. If something goes wrong, it might be very difficult to determine what data is correct and what isn't. It is very easy with this model to create (or end up in) a nightmare scenario. If you still think the Entity-Attribute-Value approach is the right one for you, then I wish you good luck. If you realize the potential problems and want to avoid it, then it would be best to redesign the database. If that is beyond your control, then you could build views to pivot the unnormalized data to a normalized set. In your case, the view could look something like this (partially copied from Plamen). You want to list all the columns in this view that belong to a Candidate. CREATE VIEW Candidate AS SELECT FK_Candidate, MAX(CASE WHEN [Key] = 'Profession' THEN [Value] END) AS profession, MAX(CASE WHEN [Key] = 'Source' THEN [Value] END) AS [source] FROM Keys JOIN Values ON Keys.key_id = Values.key_id GROUP BY FK_Candidate After that, you at least make it a bit easier for you developer to write queries, because it allows simple queries again, like SELECT FK_Candidate FROM Candidate WHERE profession = 'Informatiker' AND "source" = 'Quelle1' -- Gert-Jan SQL Server MVP |