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 |
|
stevebrett
Starting Member
1 Post |
 Posted - 2009-06-23 : 11:56:07
|
| HiWe have a problem in which we have 2 large pieces of work that are very similar. I'm looking for some best-practice pointers to find the optimal solution. I've done quite a bit of research and have my own ideas as to implementation and solution but would really appreciate it if anyone has any business experience in this kind of problem and would we willing to offer their advice.The problem is thus:We have a dozen or so 'awards' that are awarded to users once they have completed a number of training modules. Each set of training modules is composed of different entities (we already have those) and form logical groups. Within each group a user might have to pass either 4 out of 5, or (1 and 2 and3) or (4 and 5) for example. An award is created when a user has passed all the groups and has met all of the criteria in each group.The real problem we have is that the groups can be used many times in any number of awards. if a user completes a module they may get the award, but they may have met the criteria for another unspecified award that exists in the system. So we need to back calculate. We have approx 60,000 users, and maybe 50 awards that are composed of 10 or 15 out of 3 or 4 thousand modules.The advice I need is this:I naturally leaned towards the idea that the groups for sets and the awards form sets of groups so would go for a set based approach. Other guys in the team have suggested a treee based (left leaf, right leaf) structure on the basis that the searches would be faster.After googling for soultions they both seem like good ideas but was wondering what you guys thought.I can add more detail if needed, it not reall the schema I need but peoples experience of both tree based and set based searching and admin and the benefits of both. The primary driver is speed.ThanksSteve |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-06-29 : 08:54:02
|
| It's difficult without fully understanding your schema but I think you could do it by modeling your awards/groups/modules rules then joining this to the set of data representing completion.Trees can be done using CTEs but set based will almost always be faster in a relational database. |
 |
|
|
|
|
|
Complex hierarchis in SQL Server