August 28, 2007 at 11:08 pm
I currently have three different fields of data that are varchar fields that contain a comma separated list of choices that a user can select.
For the most part it does what it needs to since 90% of the time I just need to display the selection back to the user.
However I've found it rather hard to aggregate the date for reports to determine how many people choose each of the options for field.
Is there a good way of doing this, or just a better method for storing the data?
Suppose you had a table with an ID, and three csv fields food, town, color, and each of these three fields could contain 1 to 50 items selected from the list.
What would be a good way to store the data and then how would you aggregate to form a report giving you a list of how many times each color was chosen.
August 28, 2007 at 11:36 pm
I wanted to add that I understand that csv fields tend to lessen the power of a relational database.
One of the things I've been pondering is turning the three fields into three normalized tables, however I'm not sure of the best way to select the data and display it back to the user.
for example, first I'd select the user name, id, gender, age, etc
then I'd need to select from the food table all the foods he marked as his favorite, and concatenate those, then another select from the towns, and yet another from the color table, and concatenating it all together.
I hoping that there's a better way to pull this data back out without calling so many selects.
August 29, 2007 at 1:28 am
Kris
Since each user can have more than one favourite food, and each food can be favoured by more than one user, you need to create a junction table. Try something like this:
Users (UserID, UserName, Gender, Age)
Foods (FoodName, Category, ProteinContent)
UsersFoods (UserID, FoodName)
Create tables for towns, colours, etc in the same way. Then you can write a select statement something like this:
SELECT u.UserName, f.FoodName
FROM Users u JOIN UsersFoods j ON u.UserID = j.UserID
JOIN Foods f ON j.FoodName = f.FoodName
You can then use your front end to display the results in concatenated form - this is usually more efficient than asking T-SQL to perform string manipulations. If you insist on using T-SQL, search this site for "concatenated result sets" or something similar.
John
August 29, 2007 at 9:15 am
So you'd normalize each of the favorite fields into its own table
suppose you had this form on a web page
Name: Bob
Gender: 38
Age: 38
Favorite Food: food1, food2, food3
Favorite Town: town1, town2, town3
Favorite Colors: red, black,
As I see it I'd have to do 4 queries to display this page, one for the userinfo, and one for each favorite category I have. One of the things to note is that 90% of the time, the data I'll be displaying will be on a page like this.
I've pondered using this method, but I hesitant on it because it doesn't seem very efficient. Is it possible to make a view that has the results concatenated? Something like:
ID | ColorList
1 | Red,Black
2 | Green,Red
I'm also wondering which would be less efficient, making the 4 selects to the db, or 1 select that joined with the results from the three favorite views?
August 30, 2007 at 1:29 am
Kris
You might lose a little in efficiency, but what you'd gain is knowing that the integrity of your data is intact. What happens if someone has more than three favourite foods or towns or colours? Even if that doesn't happen at the moment, can you be sure there won't be a requirement for it in the future? How are you going to make sure that the same food/colour/town isn't repeated for the same person? Yes, you can use a series of constraints to do this, but that can end up just as messy or inefficient as normalising in the first place.
It is possible to get the result set you set out above in one query. As I said before, I would recommend pulling back the raw data from SQL Server, and have your web application present it in concatenated form. But if you want SQL Server to do it for you instead, there are plenty of examples of how to do it on this website - try "comma separated results" if the search string I gave before doesn't bring back what you want.
John
August 30, 2007 at 10:15 am
I agree with John. You are using a relational data to hold flat file style data.
If 90% of the time you need the csv data, then create an indexed view to hold the data, or just create a function to return you the concatonated data for you.
August 31, 2007 at 6:56 am
Normalizing the table is most likely the appropriate choice for your situation. As indicated, you can then create a view, scalar function, or custom aggregate function to put the data back together if you need to do this regularly.
If you find yourself needing multi-value fields (which should be very rare), SQL 2005 supports XML data types that are far more appropriate for this. They give you the ability to query the data much more easily that parsing a delimited field. At this point, there is really no good case remaining for a simple delimited fields for multi-value situations.
September 23, 2007 at 6:39 pm
Could someone give me some pointers?
if I had the two tables
PK User
1 Bob
2 Jim
3 Tom
Fk Role
1 Reader
1 Writer
2 Reader
3 Reader
3 Writer
3 Admin
I'd like to be able to easily and efficiently return these kinds of results
Name Roles
Bob Reader, Writer
Jim Reader
Tom Reader, Writer, Admin
Secondly in terms of efficiency, how well does this work out?
September 27, 2007 at 11:53 am
anyone?
I keep reading in places that it can be done via functions/views or something, but I'm not sure how to do this
Anyone know?
Kris
September 28, 2007 at 4:30 am
Try this. It may not be the best way but it works:
Create table users (PK int, varchar (20))
create table roles (fk int, role varchar (20))
insert into users select 1, 'Bob' union select 2, 'Jim' union select 3, 'Tom'
insert into roles select 1, 'Reader' union select 1, 'Writer' union select 2, 'Reader' union select 3, 'reader' union select 3, 'Writer' union select 3, 'Admin'
create function dbo.fnConcatRoles (@fk int)
RETURNS varchar(8000) AS
BEGIN
declare @Roles varchar(8000);
set @Roles = ''
SELECT @Roles = @Roles + ', ' + role FROM roles
WHERE fk = @fk
ORDER BY role;
set @Roles = Right(@Roles, Len(@Roles) - 2)
return @Roles
END
select , dbo.fnConcatRoles (pk) as 'Roles' from users
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply