Duplicated fields in my records, How and Where to handle them?

Question

Well, this is not an ordinary duplicate question. Because the duplication in certain fields (UserId and Name) in the row and I need create only one object for each UserId and still collects the other associated unique information in the row.

It's not my first question about the same issue, please refer to My First Question About This Issue

This is the database structure that I use

Users Table

UsedId    UserName
--------  ----------
1         Mike
2         Raul
3         Nick 

HasPrivileges Table

UsedId       PrivilegeId      
--------     --------------
1            1
1            2
1            3
2            2
2            3

Privileges Table

PrivilegeId      Privilege
-------------    ------------
1                Create
2                Edit
3                Delete

HasMedals Table

UsedId       MedalsId      
--------     --------------
1            1
1            2
2            2
3            1

Medals Table

MedalsId         Medal
-------------    ------------
1                C#
2                VB.NET

Now I'm having a DAL that query my database

and a BLL that has two proprties int for the id and string for the name and two list properties List and List and two more classes for the privileges and medals types

and my asp.net page (as my presentation layer) which uses ObjectDataSource as a data source for binding.

I use a method in my DAL to join all the previous table and select the UserId, UserName, Privellege, Medal and return a List of all the users.

The Problem Is the sql will need to duplicate the user id and name more than one time, each time with the value of the privilege, and again the id and user plus the privilege with every medal value. and I need to just have only ONE object to hold a user.

Example result for the query if I just selected UserId = 1

1   Mike   Create   C#
1   Mike   Edit     C#
1   Mike   Delete   C#
1   Mike   Create   VB.NET
1   Mike   Edit     VB.NET
1   Mike   Delete   VB.NET

Any ideas how to handle this situation the best way (architecture and performance wise) and Where to handle it -I mean on which layer- .

P.S : If I have other options than DataSet then I'd prefer to avoid them!

Thanks for your time guys =)

Answer 1

If I understand correctly, you are looking for a way to model one user, their medals, and privileges. A clean way to do this would be to create a class as view model that models that explicitly for you as below:

public class User
{
    public int ID { get; set; }
    public string Name { get; set; }
    public string[] Medals { get; set; }
    public string[] Privileges { get; set; }
}

Once you have this, instead of doing a join, it would be more natural to use IN statements to select your desired data. This is preferable to joins as it explicitly defines what you are looking for and avoids returning repeat data, and it is easy to iterate over the results to populate the fields of the your business object. Here is how to do this for UserID = 1 as in your example:

For the username info:

SELECT UserName 
FROM Users 
WHERE UserID = 1;

For the medals info:

SELECT Medal 
FROM Medals 
WHERE MedalID IN
    (SELECT MedalID
     FROM HasMedals
     WHERE UserID = 1);

For the Privileges:

SELECT Privilege 
FROM Privileges 
WHERE PrivilegeID IN
    (SELECT PrivilegeID
     FROM HasPrivileges
     WHERE UserID = 1);

An alternative would be to use an ORM solution such as the Entity Framework. This will manage your persistence, so you can focus on your business logic instead.

Answer 2

Can u kindly try this script which is working fine Input File UsedId UserName 1 Mike 2 Raul 3 Nick

nawk -F" " 'BEGIN{FS=" ";OFS=","} { if ($1==1) print $1,$2 }' Input File > Create.txt

nawk -F" " 'BEGIN{FS=" ";OFS=","} { if ($1==1) print $1,$2 }' Input File > Edit.txt

nawk -F" " 'BEGIN{FS=" ";OFS=","} { if ($1==1) print $1,$2 }' Input File > Delete.txt

cat Create.txt nawk -F"," 'BEGIN{FS=",";OFS=","} { if ($1==1) print $1,$2,(echo "Create":"C#"") }' > UsedId_1.txt

cat Edit.txt nawk -F"," 'BEGIN{FS=",";OFS=","} { if ($1==2) print $1,$2,(echo "Edit":"VB.NET"") }' > UsedId_2.txt

cat Delete.txt nawk -F"," 'BEGIN{FS=",";OFS=","} { if ($1==3) print $1,$2,(echo "Delete":"Java"") }' > UsedId_3.txt

The output will be the UsedId_*.txt that can be concrdiated

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

1st line will compare UsedId is 1 means will generate a Create.txt file

2 nd and 3 rd are common

4 th line will be comparitively executing 1,Mike,Create,C# 5 th and 6th are common the all the files we can merge

Regards, Suresh

Answer 3

Well let's start with WHERE to handle getting back the non-duplicate records as it is typically an easy decision. Here is how you decide: if you can eliminate the duplicates using TSQL at your stored procedure without affecting your object model and all business rules in code (meaning everything can change seamlessly) than do it at the database level. This can be solved in your scenario with a combination of SELECT DISTINCT and INNER JOINS on the original query.

If however you want to solve this problem at the database level and it fixes 1 single scenario in code but breaks another, then doing in in the stored proc is not a good idea. In this instance you could use LINQ to DataSets probably in the DAL when mapping to your objects to filter the data for that specefic scenario. LINQ to DataSets is quite powerful (or LINQ to Objects as well). You can use LINQ to DataSets by adding a reference to System.Data.DatasetExtensions.

Programming Guide (LINQ to DataSet):
http://msdn.microsoft.com/en-us/library/bb387004.aspx