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 |
|
rking999
Starting Member
2 Posts |
 Posted - 2006-09-01 : 11:15:01
|
| Hi all,If anybody can come up with a solution to the problem described I would be most grateful...We have a database table structure as follows: Workflow table | /| Stage table | /| Element table | Element template tableThe table structire is unique in that the elements are in fact dynamic columns of each stage, which in turn belong to the workflow. This design is fixed and cannot be cahnged as it is the core part of the system. It was designed this way so that a work can be constructed of any number/type of elements. There are over 500 different types. These are listed in the element template table. Now the problem. I have a requirement to generate a report from this structure which must be able to select all of the workflows whose elements match 10 different criteria. If these were columns it would be:column a = xcolumn b = xcolumn c = x etc.... and only the rows that match all the criteria would be returned. Because of the dynamic nature of the structure all the columns are effectively rows in the element table. Of which any given workflow can have 100 rows. I need to find all the workflows that have say:row1.code = x and row1.value = xrow2.code = x and row2.value = xrow3.code = x and row3.value = xI have worked out one way to do this. The sql is a follows...select * from dat_workflow_instance wi,dat_stage_instance si,dat_element_instance ei,dat_element_template et,dat_element_instance ei2,dat_element_template et2,dat_element_instance ei3,dat_element_template et3where wi.id = si.workflow_id and si.id = ei.stage_id and ei.template_id = et.idand si.id = ei2.stage_idand ei2.template_id = et2.idand si.id = ei3.stage_idand ei3.template_id = et3.idand et.code = 'GI11' and ei.value = 'MARKET'and et2.code = 'G12' and ei2.value < '24/08/2006'and et3.code = 'DATE5' and ei3.value = '24/08/2006'.....................and so on adding a new instance & template table for each new criteria. The problem being that performance will suck as the element table can have millions of rows of data.....One alternative could be to create a temporary table that has 500 columns width so as to flat structure all the possible rows into columns, but this seems to be an ugly solution as well...Can anyone come up with a way of achieving this??? Im at a loss.... |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-09-01 : 15:13:57
|
| Are you using an EAV (Entity/Attribute/Value) schema?Post the actual DDL of your tables. |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-01 : 20:25:18
|
| Looks like another EAV disaster.You can argue for the EAV approach, but you will have plenty of time to realize why it’s a bad idea while you are writing queries with 40 left joins, waiting for that report that takes 12 hours to run, and trying to figure out why the Schedule Date = ‘GREEN’ and Weight = ‘Rich Corinthian Leather’.I can't really offer any help, other than to say than you would be better off constructing a normalized data model. Your idea of creating a temporary table that has 500 columns is actually an acknowledgement that is what you need to do.CODO ERGO SUM |
|
|
|
rking999
Starting Member
2 Posts |
Posted - 2006-09-04 : 05:52:44
|
| Thanks for the advice. Yes, now that you both mention it, it does resemle an Entity/Value/Attribute schema, but only for the one Element table. Unfortunately, we need to have this EAV schema due to the generic nature of system. Does anybody know a recommended approach for generating reports from this type of schema efficiently? |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-09-04 : 09:23:29
|
| "Efficient" how? You can actually code EAV queries pretty compactly, but they are not going to execute with anything near the speed of 3rd normal form. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-05 : 00:09:21
|
| I doubt that you really need the EAV schema for everything. I think what you have posted shows that you have a lot of common values.I really don't see the value that EAV is supposed to bring, compared to just creating new tables as needed. The whole thing just seems like a excuse to give up on the job of modeling the data. The trouble is that most people that go this way don't realize what a terrible price thay will pay later.CODO ERGO SUM |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-09-05 : 10:50:07
|
| ...and SQL Server 2005's XML support should suffice for any application where you may have been tempted to use an EAV schema. |
|
|
|
|
|
|
|
|
SQL experts please help! Challenge ahead..