Join Arbitrary Number of Detail Records to a Master Record in a Single Row

Question

Hello everyone,

I am building a database that will hold a master record for an event, along with an arbitrary number of key-value pair detail records for that same event.  I would like to have a read-only view over these tables that returns the master record and all existing detail records for each event as a single row.  Here is some example data:

Master Event Table (ID, Name)

1 First Event
2 Second Event
3 Third Event

Event Details (Event ID, Key, Value)

1 HostName testhost.testurl.com
1 IPAddress 172.145.1.1
2 HostName testhost.testurl.com
2 IPAddress 172.145.1.1
2 Message Lost Connection
3 HostName anothertesthost.testurl.com
3 IPAddress 199.33.44.33
3 Message Exception
3 Exception MSG01999:  No Data Found

Is it possible to do this with T-SQL in a view when there are an arbitrary number of detail records, or will I have to build a temp table or some other intermediate mechanism to create the read only view of these records?  There will be a large number of records in the database and it will be highly volatile, so I am looking for the most high performance read mechanism that marries the master record to the detail records in a web based viewer.

Thank you so much for any help that you can give us.

---

Walt Smith Senior Architect

Answer 1

It is not beneficial to do this at the db side, but if you insist then search in this forum for "FOR XML PATH", which is the common way to do concatenation aggregates.

---

AMB

Some guidelines for posting questions...

Answer 2

That's good advice.  So if I don't want to do this aggegation to happen on the SQL Server, then I have one more question please.

Would it be a more efficient practice to create queries to pull the master and detail records back separately, making two round trips to the SQL Server from the application layer, or would it be more efficient to create a join that brings back each detail record along with the master record, thus performing the retrieval in one round trip to the server, but duplicating the master data for each detail row?  It seems to me that either method creates latency in the application that could be averted by having SQL Server aggregate the data set before it transfers it back to the application, but it seems that SQL Server doesn't support this type of aggregation very well.  Am I missing another architectural solution that should be obvious for the master/detail record set retrieval?

---

Walt Smith Senior Architect

Answer 3

I would get everything in one trip. What you're using in your application - EF, LINQ to SQL or plain ADO?

Say, we're using ADO.NET and I was discussing one problem today with my co-worker we don't know a good solution for. We have SPs returning multiple result sets. We also want to be able to implement Sql Transactions to be able to rollback in our unit tests. However, SQL Transactions are not allowed when connection allows multiple result sets.

Wondering if someone has a solution.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

We are using EF 4 from an ASP.NET MVC application.  I like the idea of making one trip to the database, however I don't like the idea of duplicating the master record information in every detail record that comes back if I use an inner join.  If I have a large number of detail records, this could result in a lot of duplicate information coming over the wire to the application.  I suppose I could use EF data sets and let the join happen in memory on the application server, but then I need a larger amount or RAM on that server and its performance might slow down.  It sure would be nice if SQL Server could put together an aggregated data set and pass it back to me as one record set.  Then I could make the call asynchronously to the database and just wait for the result set to come back in one go.

---

Walt Smith Senior Architect

Answer 5

I see. Can you test both scenarios? It looks like you may want to keep your first approach then. How many main rows you're getting?

Also, in EF most likely you will be using the first approach anyway if you will have a class for each table.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

>> I am building a database that will hold a master record [sic] for an event, along with an arbitrary number of key-value pair detail records [sic] for that same event. I would like to have a read-only view over these tables that returns the master record [sic] and all existing detail records [sic] for each event as a single row. <<

1. Rows are not anything like records [sic]. Among other things, the number of columns (which are not fields!) is fixed and drawn from known domains modeled with fixed data types.
2. RDBMS does not use the key-value pair data model (Google EAV as a design flaw)
3. The term “master” is not used in RDBMS; that was network databases and mag tapes. We have referencing and referenced tables.
4. In the standard temporal models, an event has a duration shown by a (start_time, end_time) pair using the ISO half-open interval model

>> Is it possible to do this with T-SQL in a view when there are an arbitrary number of detail records [sic], or will I have to build a temp table or some other intermediate mechanism to create the read only view of these records [sic]? There will be a large number of records [sic] in the database and it will be highly volatile, so I am looking for the most high performance read mechanism that marries the master record [sic] to the detail records [sic] in a web based viewer. <<

This is not RDBMS! Find another tool, probably in the NoSQL world or XML.

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL