2

I have one table Person:

Id Name 1 Person1 2 Person2 3 Person3

And I have its child table Profile:

Id PersonId FieldName Value 1 1 Firstname Alex 2 1 Lastname Balmer 3 1 Email [email protected] 4 1 Phone +1 2 30004000

And I want to get data from these two tables in one row like this:

Id Name Firstname Lastname Email Phone 1 Person1 Alex Balmer [email protected] +1 2 30004000
  1. What is the most optimized query to get these vertical (key, value) values in one row like this? Now I have a problem that I done four joins of child table to parent table because I need to get these four fields. Some optimization is for sure possible.
  2. I would like to be able to modify this query in easy way when I add new field (key,value). What is the best way to do this? To create some stored procedure?

I would like to have strongly types in my DB layer (C#) and using LINQ (when programming) so it means when I add some new Key, Value pair in Profile table I would like to do minimal modifications in DB and C# if possible. Actually I am trying to get some best practices in this case.

Improve this question
1
  • 1
    If you know at design time which columns the query should emit, why do you need an open-ended field structure in the Profile table? Commented Apr 15, 2010 at 9:33

2 Answers 2

Reset to default
1 
Select P.ID , P.Name , Case When C.FieldName = 'FirstName' Then C.Value Else NULL END AS FirstName , Case When C.FieldName = 'LastName' Then C.Value Else NULL END AS LastName , Case When C.FieldName = 'Email' Then C.Value Else NULL END AS Email , Case When C.FieldName = 'Phone' Then C.Value Else NULL END AS Phone From Person AS P Inner JOIN Child AS C ON P.ID = C.PersonID

You could use PIVOT; not sure which one would be the easiest for you to add a new column.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

best optimized way with strongly typed fields, is to do it this way:

CREATE TABLE Persons (PersonID int identity(1,1) primary key ,Firstname varchar(...) ,Lastname varchar(...) ,Email varchar(...) ,Phone varchar(...) ,.... )

then the most optimized query would be:

SELECT PersonID,Firstname,Lastname,Email,Phone FROM Persons WHERE ...

Add all main columns into the persons table. if you need to specialize create additional tables:

--one person can play many instruments with this table CREATE TABLE PersonMusicians (PersonID int --pk fk to Persons.PersonID ,InstrumentCode char(1) --pk ,... ) --only one row per person with this table CREATE TABLE PersonTeachers (PersonID int --pk fk to Persons.PersonID ,FavoriteSubjectCode char(1) ,SchoolName varchar(...) )

if you have to have unlimited dynamic attribute fields, then I would create the above structure as fully as possible (as many common fields as possible) and then have an "AdditionalInfo" table where you store all the info like:

AdditionalInfoFields FieldID int identity(1,1) primary key FieldName varchar(...) AdditionalInfo AdditionalInfoID int identity(1,1) primary key PersonID int fk to Persons.PersonID FieldID int fk to AdditionalInfoFields.FieldID FieldValue varchar(..) or you can look into sql_variant

have an index on AdditionalInfo.PersonID+FieldID and if you will search for all people that have attribute X, then also another like AdditionalInfo.FieldID+PersonID

short of any of the above, you will need to use the four left outer joins like you have mentioned in your option #1:

SELECT P.ID, p.Name , p1.Value AS Firstname , p2.value AS Lastname , p3.Value AS Email , p4.Value AS Phone FROM Persons p LEFT OUTER JOIN Profile p1 ON p.PersonID=p1.PersonID AND p1.FieldName='Firstname' LEFT OUTER JOIN Profile p1 ON p.PersonID=p1.PersonID AND p1.FieldName='Lastname' LEFT OUTER JOIN Profile p1 ON p.PersonID=p1.PersonID AND p1.FieldName='Email' LEFT OUTER JOIN Profile p1 ON p.PersonID=p1.PersonID AND p1.FieldName='Phone' WHERE ....

you could always make a materialized view with an index out of this 4 left join query and have the data precalculated for you which should speed it up.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.