Db redesign help - SQL Server Forums

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.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Db redesign help

Author

Topic

jameslh
Starting Member

9 Posts

Posted - 2007-12-03 : 17:19:03

We have a database of about 1000 products or so. The problem is that currently we have an attribute table like this:


ProductID     Attribute    Value 
10001         4            Chrome
10001         4            Nickel
10001         6            16.34
10001         7            Silver

Where the attribute names are stored in another table. This was not done by me, and I know this is a bad way to store data. The problem is that some of the products are very different and can have many different attributes which is why they originally chose this method. Some of the products have multiple values for some attributes, such as different finishes or colors that the customer can select.

There seems like there should be a better way to do this where I can keep all the column datatypes correct! Does anyone know of a more efficient way to do this?

Thanks,
James

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-12-03 : 17:50:03

This is a very tricky problem to solve well and I am glad you appreciate the limitation of the current design.
The best compromise I have found is to have extra typed columns (String1, String2, int1, int2, date1, date2 etc) either on your main table or as an 'extra data table'. You can index some (for example xxx1 would have an index). This strongly-typed relational data can be used for searching etc. The model you currently use can be used to store other data that is unlikely to be searchable or truly is user defined.
It's not great but it gets you there. Often (but not always) these ultra-flexible things come from inadequate requirements. Remember though to give the client a truly flexible system you're not that far off from giving them a copy of SQL Server or MS Access!

jameslh
Starting Member

9 Posts

Posted - 2007-12-03 : 18:13:54

I think I understand what you're saying - add extra columns onto the attribute table like:


ProductID   Attribute   Value   Text1   Text2  Int1   Int2
10001       4                   Chrome  Nickel
10001       6                                  16     25

Is that right? I guess that might work... just seems like there would be a more elegant solution. Thanks for the idea though, I may end up doing that.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-03 : 18:16:10

I don't see a problem with the current design. We've got that same design in many of our databases. We haven't experienced any performance problems related to it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-03 : 19:34:05

here's an amusing essay on why EAV can be a pain:

http://weblogs.sqlteam.com/davidm/articles/12117.aspx

elsasoft.org

jameslh
Starting Member

9 Posts

Posted - 2007-12-04 : 10:54:34

Well maybe if I explain my problem a little more, you guys can see why I'm having problems with it. I need to make a way to filter items by certain criteria. So I'm trying to get to a point where I have results like this:


Color   Count
Blue    27
Green   7

Finish   Count
Satin    6
Nickel   45

The count needs to change whenever they make a selection so that if they pick blue, then Finish count needs to show how many are blue with a satin finish? Make sense? So right now I pull in my EAV table and use PIVOT to rotate the data... But I'm still having problems because I first have to select distinct values for each attribute and then do the counts. Maybe someone knows a way I could do this in one step?
Thanks for the responses guys, and jez, that was a very good article. Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 11:09:24

[code]DECLARE @Sample TABLE (ProductID INT, Attribute INT, Value VARCHAR(60))

INSERT @Sample
SELECT 10001, 4, 'Chrome' UNION ALL
SELECT 10001, 4, 'Nickel' UNION ALL
SELECT 10001, 6, '16.34' UNION ALL
SELECT 10001, 7, 'Silver'

SELECT Value AS Color,
COUNT(DISTINCT ProductID) AS [Count]
FROM @Sample
WHERE Attribute = 4
GROUP BY Value

SELECT Value AS Finish,
COUNT(DISTINCT ProductID) AS [Count]
FROM @Sample
WHERE Attribute = 7
GROUP BY Value[/code]

E 12°55'05.25"
N 56°04'39.16"

jameslh
Starting Member

9 Posts

Posted - 2007-12-04 : 11:34:13

Hey, awesome! But wait... will I be able to filter the counts like this? If a customer selects blue for the color, the count for finish needs to change to reflect how many of each finish are blue in color. So I think the above code puts me at the same place that I am now, but at least it gets me there in a more efficient way! : )

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 11:37:54

Please provide more sample data in the format I did if you want additional help.

What exactly is going on?
How do the database know the user selected "blue"?

E 12°55'05.25"
N 56°04'39.16"

jameslh
Starting Member

9 Posts

Posted - 2007-12-04 : 11:59:47

Sorry for my lack of clarification... still a little new to this. There are several drop-down select menus where the user can specify a color or other attribute. They look something like this:


Select a Color
Blue  (44)
Green (2)
Red   (65)

Select a Finish
Satin  (34)
Chrome (23)
Nickel (2)

But when they select, say blue for example, I need to update the counts in Finish to reflect how many of each Finish are blue...


Select a Color
Blue (44)  -- SELECTED BY USER
Green (0)
Red   (0)

Select a Finish (How many are blue of each finish?)
Satin (5)
Chrome (7)
Nickel (0)

But there are more like 20 or so attributes that need to update based on this (Material,Length, etc) And each time they select another option, I have to update the counts to reflect how many are of the types they have selected. Maybe this is not as difficult as I am making it?


DECLARE	@Sample TABLE (ProductID INT, Attribute INT, Value VARCHAR(60))

INSERT	@Sample
SELECT	10001, 4, 'Blue' UNION ALL
SELECT	10001, 4, 'Green' UNION ALL
SELECT	10001, 6, 'Satin' UNION ALL
SELECT	10001, 6, 'Nickel'

SELECT		Value AS Color,
		COUNT(DISTINCT ProductID) AS [Count]
FROM		@Sample
WHERE		Attribute = 4
AND             Value = 'Blue'
GROUP BY	Value

SELECT		Value AS Finish,
		COUNT(DISTINCT ProductID) AS [Count]
FROM		@Sample
WHERE		Attribute = 6
AND             Value = 'Blue' <--- Not going to work.
GROUP BY	Value

Does that help? This is why I originally thought that this type of storage structure was a bad idea.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 12:03:38

Where is the proper sample data?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 12:08:18

[code]-- Prepare sample data
DECLARE @Sample TABLE (ProductID INT, Attribute INT, Value VARCHAR(60))

INSERT @Sample
SELECT 10003, 6, 'Steel' UNION ALL
SELECT 10001, 4, 'Blue' UNION ALL
SELECT 10001, 4, 'Green' UNION ALL
SELECT 10001, 6, 'Satin' UNION ALL
SELECT 10001, 6, 'Nickel' UNION ALL
SELECT 10002, 4, 'Yellow' UNION ALL
SELECT 10002, 4, 'Green' UNION ALL
SELECT 10002, 6, 'Nickel'

-- Select colors
SELECT Value AS Color,
COUNT(DISTINCT ProductID) AS [Count]
FROM @Sample
WHERE Attribute = 4
GROUP BY Value

-- Select finish for products with blue color
SELECT s1.Value AS Finish,
COUNT(DISTINCT s2.ProductID) AS [Count]
FROM @Sample AS s1
LEFT JOIN @Sample AS s2 ON s2.ProductID = s1.ProductID
AND s2.Attribute = 4
AND s2.Value = 'Blue'
WHERE s1.Attribute = 6
GROUP BY s1.Value[/code]

E 12°55'05.25"
N 56°04'39.16"

jameslh
Starting Member

9 Posts

Posted - 2007-12-04 : 12:13:33

Oh so for each attribute, I'll have to do a self join... This is gonna get messy! Thanks so much for your response, you really helped me out.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 12:26:41

No, you dno't have to!
See this code

-- Prepare sample data
DECLARE	@Sample TABLE (ProductID INT, Attribute INT, Value VARCHAR(60))

INSERT	@Sample
SELECT	10001, 4, 'Blue' UNION ALL
SELECT	10001, 4, 'Green' UNION ALL
SELECT	10001, 6, 'Satin' UNION ALL
SELECT	10001, 6, 'Nickel' UNION ALL
SELECT	10002, 4, 'Yellow' UNION ALL
SELECT	10002, 4, 'Green' UNION ALL
SELECT	10002, 6, 'Nickel' UNION ALL
SELECT	10003, 4, 'Yellow' UNION ALL
SELECT	10003, 6, 'Steel'

DECLARE	@Color VARCHAR(60)
SET	@Color = 'Yellow'	-- NULL for all colors

-- Select all colors with certain preference if available
SELECT		Value AS Color,
		COUNT(ProductID) AS [Count]
FROM		(
			SELECT DISTINCT	Value,
					CASE
						WHEN Value = ISNULL(@Color, Value) THEN ProductID
					END AS ProductID
			FROM		@Sample
			WHERE		Attribute = 4
		) AS q
GROUP BY	Value

-- Select finish for products with chosen color
SELECT		s1.Value AS Finish,
		COUNT(DISTINCT s2.ProductID) AS [Count]
FROM		@Sample AS s1
LEFT JOIN	@Sample AS s2 ON s2.ProductID = s1.ProductID
			AND s2.Attribute = 4
			AND ISNULL(@Color, s2.Value) = s2.Value
WHERE		s1.Attribute = 6
GROUP BY	s1.Value

Replace the color assignment with another color or use NULL for all colors.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 12:52:06

Is it working for you?

E 12°55'05.25"
N 56°04'39.16"

jameslh
Starting Member

9 Posts

Posted - 2007-12-04 : 13:08:50

Well what I mean is the Finish attribute will have to work in the same way. So I have many attributes - Color, Finish, Material, Configuration, etc.

So for each one that is selected it will need to join the table to itself like you did in the Finish section above.


-- Finish attribute
SELECT		s1.Value AS Finish,
		COUNT(DISTINCT s2.ProductID) AS [Count]
FROM		@Sample AS s1
LEFT JOIN	@Sample AS s2 ON s2.ProductID = s1.ProductID
			AND s2.Attribute = 4
			AND ISNULL(@Color, s2.Value) = s2.Value
LEFT JOIN	@Sample AS s3 ON s3.ProductID = s1.ProductID
			AND s3.Attribute = 10
			AND ISNULL(@Material, s3.Value) = s3.Value
WHERE		s1.Attribute = 6
GROUP BY	s1.Value

Or something like that? So for each attribute I'll need to join @Sample AS s[num] -- But maybe I'm just not understanding correctly.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 13:40:57

If you cared about a viable solution, please provide some proper and accurate sample data.
Also provide expected output based on the provided sample data.

I would also like to know all details about this assignment. How many drop downs are there?
Is there a hierarchy?

E 12°55'05.25"
N 56°04'39.16"

jameslh
Starting Member

9 Posts

Posted - 2007-12-04 : 14:23:25

I really appreciate your help, is this enough data to make sense of it?


-- Prepare sample data
DECLARE	@Sample TABLE (ProductID INT, Attribute INT, Value VARCHAR(60))

INSERT	@Sample
SELECT	1000250,  7, 'Double Bowl' UNION ALL
SELECT	1000250,  4, '19.125' UNION ALL
SELECT	1000250,  3, '33.25' UNION ALL
SELECT	1000250,  9, 'Integrated' UNION ALL
SELECT	1000250, 13, 'Bisque' UNION ALL
SELECT	1000360,  7, 'Single Bowl' UNION ALL
SELECT	1000360,  4, '10.5' UNION ALL
SELECT	1000360,  3, '14.63' UNION ALL
SELECT	1000360,  9, 'Undermount' UNION ALL
SELECT	1000360, 13, 'Cameo White'
SELECT	1000362,  7, 'Single Bowl' UNION ALL
SELECT	1000362,  4, '10.5' UNION ALL
SELECT	1000362,  3, '14.63' UNION ALL
SELECT	1000362,  9, 'Integrated' UNION ALL
SELECT	1000362, 13, 'Vanilla'

3=Length 4=Width 7=Configuration 9=InstallType 13=Color

There are other values (a few to many to put in here), but I think those can be left out for explanation purposes.

In reality there are 22 different attributes but not all are searchable via the dropdowns on the page. Only about 10 different dropdowns are searchable, but I think if I can understand how I can do 3 or 4, I can do them all. There is no hierarchy in these dropdowns.

So for instance. User chooses: Configuration - 'Single Bowl'
The select dropdowns then will update based on this data and will display the following:


Configuration
Single Bowl (2) <-- Selected
Double Bowl (0)

Length
10-20 (2)
20-50 (0)
50-90 (0)

Width
10-15 (2)
15-20 (0)
20-30 (0)

InstallType
Undermount (1)
Integrated (1)

Color
Vanilla (1)
Cameo White (1)
Bisque (0)

So if the customer now selects another option such as: InstallType - Undermount

Configuration
Single Bowl (1) <-- Selected (only one single bowl / undermount )
Double Bowl (0)

Length
10-20 (1)
20-50 (0)
50-90 (0)

Width
10-15 (1)
15-20 (0)
20-30 (0)

InstallType
Undermount (1) <--- User selected
Integrated (0)

Color
Vanilla (0)
Cameo White (1)
Bisque (0)
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 14:46:51

Yes, I understand this.
But how do you notify the stored procedure in the database after every user selection?

Oh, I see now you want some ClikView functionality...

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 16:50:48

[code]-- Prepare sample data
DECLARE @Sample TABLE (ProductID INT, Attribute INT, Value VARCHAR(60))

INSERT @Sample
SELECT 1000250, 7, 'Double Bowl' UNION ALL
SELECT 1000250, 4, '19.125' UNION ALL
SELECT 1000250, 3, '33.25' UNION ALL
SELECT 1000250, 9, 'Integrated' UNION ALL
SELECT 1000250, 13, 'Bisque' UNION ALL
SELECT 1000360, 7, 'Single Bowl' UNION ALL
SELECT 1000360, 4, '10.5' UNION ALL
SELECT 1000360, 3, '14.63' UNION ALL
SELECT 1000360, 9, 'Undermount' UNION ALL
SELECT 1000360, 13, 'Cameo White' UNION ALL
SELECT 1000362, 7, 'Single Bowl' UNION ALL
SELECT 1000362, 4, '10.5' UNION ALL
SELECT 1000362, 3, '14.63' UNION ALL
SELECT 1000362, 9, 'Integrated' UNION ALL
SELECT 1000362, 13, 'Vanilla'

DECLARE @Attributes TABLE (AttributeID INT, AttributeName VARCHAR(60), SortOrder TINYINT)

INSERT @Attributes
SELECT 3, 'Length', 2 UNION ALL
SELECT 4, 'Width', 3 UNION ALL
SELECT 7, 'Configuration', 1 UNION ALL
SELECT 9, 'InstallType', 4 UNION ALL
SELECT 13, 'Color', 5

-- Initialize user selection
DECLARE @UserSelections TABLE (Attribute INT, Value VARCHAR(60))

INSERT @UserSelections
--SELECT 9, 'Undermount' UNION ALL
SELECT 7, 'Single Bowl'

-- Show the expected output
SELECT a.AttributeName,
s.Value,
COUNT(DISTINCT q.ProductID) AS [Count]
FROM (
SELECT ProductID,
Attribute,
CASE
WHEN Attribute = 3 AND CAST(Value AS SMALLMONEY) >= 10 AND CAST(Value AS SMALLMONEY) < 20 THEN '10-20'
WHEN Attribute = 3 AND CAST(Value AS SMALLMONEY) >= 20 AND CAST(Value AS SMALLMONEY) < 50 THEN '20-50'
WHEN Attribute = 3 AND CAST(Value AS SMALLMONEY) >= 50 AND CAST(Value AS SMALLMONEY) < 90 THEN '50-90'
WHEN Attribute = 4 AND CAST(Value AS SMALLMONEY) >= 10 AND CAST(Value AS SMALLMONEY) < 15 THEN '10-15'
WHEN Attribute = 4 AND CAST(Value AS SMALLMONEY) >= 15 AND CAST(Value AS SMALLMONEY) < 20 THEN '15-20'
WHEN Attribute = 4 AND CAST(Value AS SMALLMONEY) >= 20 AND CAST(Value AS SMALLMONEY) < 30 THEN '20-30'
ELSE Value
END AS Value
FROM @Sample
/*
UNION ALL

-- Add ghost records to ensure all combinations are shown
SELECT NULL, 3, '10-20' UNION ALL
SELECT NULL, 3, '20-50' UNION ALL
SELECT NULL, 3, '50-90' UNION ALL
SELECT NULL, 4, '10-15' UNION ALL
SELECT NULL, 4, '15-20' UNION ALL
SELECT NULL, 4, '20-30'
*/
) AS s
INNER JOIN @Attributes AS a ON a.AttributeID = s.Attribute
LEFT JOIN (
SELECT s.ProductID
FROM @Sample AS s
INNER JOIN @UserSelections AS u ON u.Attribute = s.Attribute
AND u.Value = s.Value
GROUP BY s.ProductID
HAVING COUNT(*) >= (SELECT COUNT(*) FROM @UserSelections)
) AS q ON q.ProductID = s.ProductID
GROUP BY a.SortOrder,
a.AttributeName,
s.Value
ORDER BY a.SortOrder,
s.Value[/code]

E 12°55'05.25"
N 56°04'39.16"

jameslh
Starting Member

9 Posts

Posted - 2007-12-04 : 17:00:21

Woah! I think you nailed it man! I've been trying to do that for a full two days now. It's going to take me a while to try and implement it, but it looks like that would do it! I'm seriously in awe of how quickly you got that. Thanks a million!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources