February 2, 2016 at 12:06 pm
In a sql server 2012 database, I have the following table that I need to obtain data from by doing a lot of self joins.
CREATE TABLE [dbo].[CustomCustomer](
[customID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[personID] [int] NOT NULL,
[attributeID] [int] NOT NULL,
[value] [varchar](256) NULL,
[date] [smalldatetime] NULL,
[customGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[districtID] [int] NULL,
GO
LTER TABLE [dbo].[CustomCustomer] WITH NOCHECK ADD CONSTRAINT [FK_CustomerData_Person] FOREIGN KEY([personID])
REFERENCES [dbo].[Person] ([personID])
GO
ALTER TABLE [dbo].[CustomCustomer] CHECK CONSTRAINT [FK_CustomerData_Person]
GO
ALTER TABLE [dbo].[CustomCustomer] WITH NOCHECK ADD CONSTRAINT [FK_CustomerData_CustomerAttribute] FOREIGN KEY([attributeID])
REFERENCES [dbo].[CustomerAttribute] ([attributeID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomCustomer] CHECK CONSTRAINT [FK_CustomerData_CustomerAttribute]
GO
I am posting this t-sql question since I would like to know the best way to obtain the required data I need from this table. Basically
data is obtained from this table by looking for the unique values in the attributeID column for each customer that is identified by personID.
The values are unique for a 'group' of atrribute values by looking at the date column of this table.
Customers can have unique categories like location, business type, inventory, and quantity on hand. When data is entered into the group of attribute categories the date
field is used to uniquely identify the group of values that were entered.
Basically I need to obtain data for each customer by the groupings of location (attribute values between 100 and 150) ,inventory (attribute values between 200 and 250) and quantity on
hand (attribute values between 900 and 915). I need to obtain the values from Oct 1. 2015 to Dec. 31 2015.
Thus can you show me the t-sql on how tgo accomplish this goal by using self-joins and/or whatever you recomend to be used?
February 2, 2016 at 12:40 pm
Can you post sample data with expected results?
This seems like an EAV design which wouldn't need self joins, it only needs cross tabs using the MAX() aggregate function.
Here's a basic example, but you can modify the conditions or the columns that define a row.
SELECT PersonID,
MAX(CASE WHEN attributeID = 100 THEN value END) AS Attribute100,
MAX(CASE WHEN attributeID = 101 THEN value END) AS Attribute101,
MAX(CASE WHEN attributeID = 102 THEN value END) AS Attribute102,
MAX(CASE WHEN attributeID = 103 THEN value END) AS Attribute103
FROM CustomCustomer
GROUP BY personID;
February 2, 2016 at 12:59 pm
What is EAV design? The date value needs to be used to keep the correct group of values.
February 2, 2016 at 1:02 pm
wendy elizabeth (2/2/2016)
What is EAV design? The date value needs to be used to keep the correct group of values.
First two hits on Google:
https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model
http://programmers.stackexchange.com/questions/93124/eav-is-it-really-bad-in-all-scenarios
If you want more help to get the query right, then reread what Luis wrote: post sample data (as INSERT statements that execute against the CREATE TABLE you already posted) and expected results.
February 2, 2016 at 1:34 pm
This has to be one of my favorite stories about an EAV system. https://www.simple-talk.com/opinion/opinion-pieces/bad-carma/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 2, 2016 at 8:17 pm
8 years ago I also was asked to work on "EAV Magic" system. "EVA Magic" was not quite as extreme as "vision". It stored the meta data in a small number of tables. Every piece of actual data in the system was stored in a single table varchar(max) column. After successfully building a number of systems over the years with more conventional relational models, I was adamantly opposed to the design. My "Punishment" was that I was left to work on the old system that "EAV Magic" was going to replace. After two years the "EAV Magic" system never came even close to working and was cancelled. The system that was going to be replaced is still in production today.
EAV is like socialism. It never works, but some very smart people keep believing that it will solve all the worlds problems, despite all the past failures. Back then I asked on one of these forums if anyone had ever seen a design like this succeed and some one recommended this article. An oldie, but goodie.
I will say that in system with simple logic, not too many fields, and very limited levels of hierarchical type relationships you might get something like this to work with a website. The problem is that reporting on this kind of data is a nightmare.
A good data model does require time to design implement and maintain, but it makes everything else you need to do in the system easier and faster. If you seriously have a table wit a column named "Value" and it is not being used for configuration data you need some lessons in database design and relational theory. If you are being forced by someone else to use this design "Run Like Hell".
February 2, 2016 at 10:14 pm
I know what EAV is good for.
A search.
A search for value in one of the field.
Instead of building one of those "catch all" queries store all the values in an EAV table and run a search query against it.
And yes, values must be stored as sql_variant, not varchar(max).
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply