Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MPEvans
Starting Member
7 Posts |
 Posted - 2006-02-16 : 06:53:54
|
| I know not many people like the EAV model very much, but our product sits very closely to the clinical trials field and it seems to suit it well. I'm happy to debate the EAV suitability topic as I'm genuinely interested in experiences etc (rather than rhetoric and stubborn mindedness!), but the real purpose of this post is to do with re-assembling the vertical columns of data into single row.I currently do the reassembly in the data access layer and business logic layer of the client app which works well for the limited (eg page size) sets of data, but reporting is of course an important aspect and I think there is a need to have a pre-prepared view in the database itself.The structure is simple; a header table with a PK headerId and a child item table that has it's own key, the headerID as a FK, a value and a FK to identify the type of the value. EG:headerID itemID fieldID fieldvalue12 1 3 Toyota12 2 6 200012 3 3 Corolla12 4 1 01-12-2005What I'd like to be able to do is create a column per fieldID:headerID Brand(3) Engine size(6) Model(3) Prod.Date(1)12 Toyota 2000 Corolla 01-12-2005Of course, I don't know how many columns there may be per header. In this example I've got just 4, but there could be 50.Any thoughts?Thanks,Martin |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-02-16 : 07:51:16
|
If for a specific report, the number of column is known, it can be easily done with this.select headerID, max(case when fieldID = 2 then fieldvalue else null end) as [Brand], max(case when fieldID = 6 then fieldvalue else null end) as [Engine size], max(case when fieldID = 3 then fieldvalue else null end) as [Model], max(case when fieldID = 1 then fieldvalue else null end) as [Date]from #eavwhere headerID = 12group by headerID If not, you will have to use Dynamic SQL to achieve this.----------------------------------'KH'Time is always against us |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
MPEvans
Starting Member
7 Posts |
Posted - 2006-02-16 : 12:10:40
|
| KH,Thanks, they dynamic SQL version is working well.RegardsMartin |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-02-16 : 15:48:48
|
| If you are interested in a discussion about EAV, take a look at this "real-world" thread.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61024The type of query you see there is where you are headed. I think Brett's comment sums it up best: "I just threw up"The EAV model seems seductive when you start with it; no data modeling work to do. Then later on you pay back that small initial savings a thousand times over trying to keep it running.This is your future: Nightmarish queries, horrible performance, query plans that look like integrated circuit diagrams, impossible to debug data integrity issues, phone ringing off the hook, hours spent looking at Query Analyzer hoping that stored procedure runs in less than 2 days this time while everyone stands around asking why you can't just fix it.Yes, I have been there with EAV. My experence is as real and painful as it gets.Good luck.CODO ERGO SUM |
|
|
|
MPEvans
Starting Member
7 Posts |
Posted - 2006-02-20 : 06:38:17
|
| I do understand that the EAV model comes with problems, but unless someone can tell me how else to address the concept of custom fields in a better way then this seems to be the only option.What I'm hoping to do is reduce the querying problems by having a 'traditional' header table with all the fields that would be used for reporting etc and a child table that provides the (vertical) custom fields concept. I don't plan to over-engineer the solution.My biggest issue will be the reporting. I'm currently considering building the data in the client app (based on a query of the header table) and outputting it for the end users to use in whatever reporting tool they want. Or flattening the data on a schedule into a traditional table...Our volumes are going to be reasonably small. Rarely more than 20k items in the main table as our customers are typically departmental or SMB. I'm hoping that SQLServer can deal with this. Our current platform of Domino does! Performance is, whilst obviously important, less of a concern than maybe a business critical realtime system and on balance, the flexibility of custom fields is probably more important to the users than a super fast system...Finally, like I said in the topic subject, I've got the luxury of doing a good prototype first so we can always back away gracefully later! |
|
|
|
|
|
|
|
|
EAV question for prototype app