|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Better Way To Handle The Code?I have a table which stores data in multi-rows basis for a particular record. The structure of the table is as exhibit: CREATE TABLE [dbo].[Table_Header] ( [Rec_Id] [numeric](18, 0) NOT NULL , [Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , ) ON [PRIMARY] GO Example data is shown as follows: Rec_Id Param_Name Param_Value 1 Product TV 1 Category Electrical 2 Product Telephone 2 Category Electrical 3 Product TV 3 Category Electrical In case I would like to filter out those records (Rec_Id) where Product = TV and Category = Electrical, one way I can think of using T-SQL is: Select A.Rec_Id From Table_Header A, Table_Header B Where A.Rec_Id = B.Rec_Id And A.Param_Name = 'Product' And A.Param_Value = 'TV' And B.Param_Name = 'Category' And B.Param_Value = 'Electrical' The Select statement above returns me results: 1 and 3. But it will grow to be quite complicated if I have many parameters to filter in my select statement. Any better way to handle this coding? Thanks LBT
What is a PK on your table? Do you have any indexes defined on the table? Show quote "LBT" <L**@discussions.microsoft.com> wrote in message news:A1E1B426-7986-4535-BF4B-BDD35532F273@microsoft.com... > Hi experts, > > I have a table which stores data in multi-rows basis for a particular > record. The structure of the table is as exhibit: > > CREATE TABLE [dbo].[Table_Header] ( > [Rec_Id] [numeric](18, 0) NOT NULL , > [Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , > [Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > ) ON [PRIMARY] > GO > > Example data is shown as follows: > Rec_Id Param_Name Param_Value > 1 Product TV > 1 Category Electrical > 2 Product Telephone > 2 Category Electrical > 3 Product TV > 3 Category Electrical > > In case I would like to filter out those records (Rec_Id) where Product = TV > and Category = Electrical, one way I can think of using T-SQL is: > > Select A.Rec_Id From Table_Header A, Table_Header B > Where A.Rec_Id = B.Rec_Id And > A.Param_Name = 'Product' And > A.Param_Value = 'TV' And > B.Param_Name = 'Category' And > B.Param_Value = 'Electrical' > > The Select statement above returns me results: 1 and 3. But it will grow to > be quite complicated if I have many parameters to filter in my select > statement. Any better way to handle this coding? > > Thanks > > > > > > > The PKs are Rec_Id and Param_Name
Show quote "Uri Dimant" wrote: > LBT > What is a PK on your table? > Do you have any indexes defined on the table? > > "LBT" <L**@discussions.microsoft.com> wrote in message > news:A1E1B426-7986-4535-BF4B-BDD35532F273@microsoft.com... > > Hi experts, > > > > I have a table which stores data in multi-rows basis for a particular > > record. The structure of the table is as exhibit: > > > > CREATE TABLE [dbo].[Table_Header] ( > > [Rec_Id] [numeric](18, 0) NOT NULL , > > [Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL > , > > [Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > > ) ON [PRIMARY] > > GO > > > > Example data is shown as follows: > > Rec_Id Param_Name Param_Value > > 1 Product TV > > 1 Category Electrical > > 2 Product Telephone > > 2 Category Electrical > > 3 Product TV > > 3 Category Electrical > > > > In case I would like to filter out those records (Rec_Id) where Product = > TV > > and Category = Electrical, one way I can think of using T-SQL is: > > > > Select A.Rec_Id From Table_Header A, Table_Header B > > Where A.Rec_Id = B.Rec_Id And > > A.Param_Name = 'Product' And > > A.Param_Value = 'TV' And > > B.Param_Name = 'Category' And > > B.Param_Value = 'Electrical' > > > > The Select statement above returns me results: 1 and 3. But it will grow > to > > be quite complicated if I have many parameters to filter in my select > > statement. Any better way to handle this coding? > > > > Thanks > > > > > > > > > > > > > > > > > Hi
You can normalize your table. You can have 2 tables here Product and Category Product: RecID | Product_Name 1 | TV 2 | Telephone 3 | TV Category: RecID | Category_Name 1 | Electrical 2 | Electrical 3 | Electrical Based on the req. u can query as Select Category.Rec_id FROM Category INNER JOIN Product ON Product.Rec_id = Category.Rec_id AND Product_Name IN ('TV') WHERE Category_Name IN ('Electrical') This will solve the purpose -- Show quotebest Regards, Chandra http://chanduas.blogspot.com/ http://groups.msn.com/SQLResource/ --------------------------------------- "LBT" wrote: > Hi experts, > > I have a table which stores data in multi-rows basis for a particular > record. The structure of the table is as exhibit: > > CREATE TABLE [dbo].[Table_Header] ( > [Rec_Id] [numeric](18, 0) NOT NULL , > [Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , > [Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > ) ON [PRIMARY] > GO > > Example data is shown as follows: > Rec_Id Param_Name Param_Value > 1 Product TV > 1 Category Electrical > 2 Product Telephone > 2 Category Electrical > 3 Product TV > 3 Category Electrical > > In case I would like to filter out those records (Rec_Id) where Product = TV > and Category = Electrical, one way I can think of using T-SQL is: > > Select A.Rec_Id From Table_Header A, Table_Header B > Where A.Rec_Id = B.Rec_Id And > A.Param_Name = 'Product' And > A.Param_Value = 'TV' And > B.Param_Name = 'Category' And > B.Param_Value = 'Electrical' > > The Select statement above returns me results: 1 and 3. But it will grow to > be quite complicated if I have many parameters to filter in my select > statement. Any better way to handle this coding? > > Thanks > > > > > > > Thanks for the suggestion. In fact, there will be a lot of parameters in my
actual application where I think normalization is not my exact solution (sorry that not to make my question clear in previous post). And because of the norm that the parameters list will change from time to time, I need to store data in the mentioned manner (or I will have to change table structure everytime parameters change). Show quote "Chandra" wrote: > Hi > You can normalize your table. > You can have 2 tables here Product and Category > Product: > RecID | Product_Name > 1 | TV > 2 | Telephone > 3 | TV > > Category: > RecID | Category_Name > 1 | Electrical > 2 | Electrical > 3 | Electrical > > Based on the req. u can query as > > Select Category.Rec_id > FROM Category > INNER JOIN Product ON Product.Rec_id = Category.Rec_id > AND Product_Name IN ('TV') > WHERE Category_Name IN ('Electrical') > > This will solve the purpose > > -- > best Regards, > Chandra > http://chanduas.blogspot.com/ > http://groups.msn.com/SQLResource/ > --------------------------------------- > > > > "LBT" wrote: > > > Hi experts, > > > > I have a table which stores data in multi-rows basis for a particular > > record. The structure of the table is as exhibit: > > > > CREATE TABLE [dbo].[Table_Header] ( > > [Rec_Id] [numeric](18, 0) NOT NULL , > > [Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , > > [Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > > ) ON [PRIMARY] > > GO > > > > Example data is shown as follows: > > Rec_Id Param_Name Param_Value > > 1 Product TV > > 1 Category Electrical > > 2 Product Telephone > > 2 Category Electrical > > 3 Product TV > > 3 Category Electrical > > > > In case I would like to filter out those records (Rec_Id) where Product = TV > > and Category = Electrical, one way I can think of using T-SQL is: > > > > Select A.Rec_Id From Table_Header A, Table_Header B > > Where A.Rec_Id = B.Rec_Id And > > A.Param_Name = 'Product' And > > A.Param_Value = 'TV' And > > B.Param_Name = 'Category' And > > B.Param_Value = 'Electrical' > > > > The Select statement above returns me results: 1 and 3. But it will grow to > > be quite complicated if I have many parameters to filter in my select > > statement. Any better way to handle this coding? > > > > Thanks > > > > > > > > > > > > > > > I have a table which stores data in multi-rows basis for a particular Why? That's usually a really poor design. There are better ways to> record represent types and subtypes. Example: CREATE TABLE Products (sku INTEGER PRIMARY KEY, product_type_code CHAR(2) NOT NULL CHECK (product_type_code IN ('EL','EN','SP')), productname VARCHAR(40) NOT NULL UNIQUE, UNIQUE (sku,product_type_code) /* Columns common to all products */) CREATE TABLE EntertainmentProducts (sku INTEGER PRIMARY KEY, product_type_code CHAR(2) DEFAULT 'EN' NOT NULL CHECK (product_type_code='EN'), medium CHAR(3) NOT NULL CHECK (medium IN ('DVD','VHS','CD')), FOREIGN KEY (sku, product_type_code) REFERENCES Products (sku, product_type_code) /* Columns specific to this subtype... */) CREATE TABLE ElectronicProducts (sku INTEGER PRIMARY KEY, product_type_code CHAR(2) DEFAULT 'EL' NOT NULL CHECK (product_type_code='EL'), FOREIGN KEY (sku, product_type_code) REFERENCES Products (sku, product_type_code) /* Columns specific to this subtype */) CREATE TABLE SportsProducts (sku INTEGER PRIMARY KEY, product_type_code CHAR(2) DEFAULT 'SP' NOT NULL CHECK (product_type_code='SP'), FOREIGN KEY (sku, product_type_code) REFERENCES Products (sku, product_type_code) /* Columns specific to this subtype */) > it will grow to Correct! That's why it's better to go for a more normalized approach.> be quite complicated if I have many parameters to filter in my select > statement. -- David Portas SQL Server MVP -- 1. What objects are being stored in this table? It looks like a generic
System table - holding attribute bnames and values, for a database. I would recommend you get a good book on Relational Data modeling, and take a quick look at it. You might consider normalizing the data structure a bit.. Instead of having a Param_Name column in a table, you might want to create a Products Table and a Categorys Table, but I don;t know what exactly you are doing so it's hard to tell.. Show quote "LBT" wrote: > Hi experts, > > I have a table which stores data in multi-rows basis for a particular > record. The structure of the table is as exhibit: > > CREATE TABLE [dbo].[Table_Header] ( > [Rec_Id] [numeric](18, 0) NOT NULL , > [Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , > [Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > ) ON [PRIMARY] > GO > > Example data is shown as follows: > Rec_Id Param_Name Param_Value > 1 Product TV > 1 Category Electrical > 2 Product Telephone > 2 Category Electrical > 3 Product TV > 3 Category Electrical > > In case I would like to filter out those records (Rec_Id) where Product = TV > and Category = Electrical, one way I can think of using T-SQL is: > > Select A.Rec_Id From Table_Header A, Table_Header B > Where A.Rec_Id = B.Rec_Id And > A.Param_Name = 'Product' And > A.Param_Value = 'TV' And > B.Param_Name = 'Category' And > B.Param_Value = 'Electrical' > > The Select statement above returns me results: 1 and 3. But it will grow to > be quite complicated if I have many parameters to filter in my select > statement. Any better way to handle this coding? > > Thanks > > > > > > > I think I have provided a confusing example. My mistake :)
Actually the table structure is designed in such a way to store variety of data structure sent in by user. Say for example, user might send in "Rec_Id [1], Product [TV], Category [Electrical]", "Rec_Id [2], Product [Telephone], Category [Electrical]", etc. At later time, user can send in data structure with new add-in parameter(s) - "Rec_Id [3], Product [TV], Category [Electrical], Power [1000kW], Color [Black]". The challenge is I cannot change the table structure (this is the dynamic solution requested by customer) everytime new parameter is being introduced. With the table design, I can simply store "Power" into Param_Name and "1000kW" into Param_Value (no adding of column to table structure is allowed). So with this table design, the only way I can perform records filtering (the filtering parameters however will be fixed which are Product and Category in this case) is as stated in my post? I just wonder whether there is any better way to perform the query. In fact, I have to create and configure few reference/definition tables so that my application will be dynamic enough to cater for this requirement. But it will be lengthy to explain the entire situation. Thanks Show quote "CBretana" wrote: > 1. What objects are being stored in this table? It looks like a generic > System table - holding attribute bnames and values, for a database. I would > recommend you get a good book on Relational Data modeling, and take a quick > look at it. You might consider normalizing the data structure a bit.. > Instead of having a Param_Name column in a table, you might want to create a > Products Table and a Categorys Table, but I don;t know what exactly you are > doing so it's hard to tell.. > > "LBT" wrote: > > > Hi experts, > > > > I have a table which stores data in multi-rows basis for a particular > > record. The structure of the table is as exhibit: > > > > CREATE TABLE [dbo].[Table_Header] ( > > [Rec_Id] [numeric](18, 0) NOT NULL , > > [Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , > > [Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > > ) ON [PRIMARY] > > GO > > > > Example data is shown as follows: > > Rec_Id Param_Name Param_Value > > 1 Product TV > > 1 Category Electrical > > 2 Product Telephone > > 2 Category Electrical > > 3 Product TV > > 3 Category Electrical > > > > In case I would like to filter out those records (Rec_Id) where Product = TV > > and Category = Electrical, one way I can think of using T-SQL is: > > > > Select A.Rec_Id From Table_Header A, Table_Header B > > Where A.Rec_Id = B.Rec_Id And > > A.Param_Name = 'Product' And > > A.Param_Value = 'TV' And > > B.Param_Name = 'Category' And > > B.Param_Value = 'Electrical' > > > > The Select statement above returns me results: 1 and 3. But it will grow to > > be quite complicated if I have many parameters to filter in my select > > statement. Any better way to handle this coding? > > > > Thanks > > > > > > > > > > > > > > In a way, you are in the dilemma which often results from trying to achieve
conflicting objectives... The entire concept of Relational Databases is, in one sense (I need to be careful here) designed to allow you to "Structure" your data so that it closely matches the real world data objects or abstractions which represent those objects, in order to allow the kind of data "processing" (like arbitrary filtering, sorting, etc. that you are trying to accomplish. Old style (pre-Relational) Database systems were comparitively inadeguate at these sorts of things, *because* the data was not stored in a structured relational way. But you are *Trying* to store your data in an unstructured way to allow as much flexibility in what kind of data is allowed in to the database. These two objectives are fundamentally inconsistent, and that is one reason why you are experiencing difficulties Show quote "LBT" wrote: > I think I have provided a confusing example. My mistake :) > > Actually the table structure is designed in such a way to store variety of > data structure sent in by user. Say for example, user might send in "Rec_Id > [1], Product [TV], Category [Electrical]", "Rec_Id [2], Product [Telephone], > Category [Electrical]", etc. > > At later time, user can send in data structure with new add-in parameter(s) > - "Rec_Id [3], Product [TV], Category [Electrical], Power [1000kW], Color > [Black]". The challenge is I cannot change the table structure (this is the > dynamic solution requested by customer) everytime new parameter is being > introduced. With the table design, I can simply store "Power" into Param_Name > and "1000kW" into Param_Value (no adding of column to table structure is > allowed). > > So with this table design, the only way I can perform records filtering (the > filtering parameters however will be fixed which are Product and Category in > this case) is as stated in my post? I just wonder whether there is any better > way to perform the query. > > In fact, I have to create and configure few reference/definition tables so > that my application will be dynamic enough to cater for this requirement. But > it will be lengthy to explain the entire situation. > > Thanks > > "CBretana" wrote: > > > 1. What objects are being stored in this table? It looks like a generic > > System table - holding attribute bnames and values, for a database. I would > > recommend you get a good book on Relational Data modeling, and take a quick > > look at it. You might consider normalizing the data structure a bit.. > > Instead of having a Param_Name column in a table, you might want to create a > > Products Table and a Categorys Table, but I don;t know what exactly you are > > doing so it's hard to tell.. > > > > "LBT" wrote: > > > > > Hi experts, > > > > > > I have a table which stores data in multi-rows basis for a particular > > > record. The structure of the table is as exhibit: > > > > > > CREATE TABLE [dbo].[Table_Header] ( > > > [Rec_Id] [numeric](18, 0) NOT NULL , > > > [Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , > > > [Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > > > ) ON [PRIMARY] > > > GO > > > > > > Example data is shown as follows: > > > Rec_Id Param_Name Param_Value > > > 1 Product TV > > > 1 Category Electrical > > > 2 Product Telephone > > > 2 Category Electrical > > > 3 Product TV > > > 3 Category Electrical > > > > > > In case I would like to filter out those records (Rec_Id) where Product = TV > > > and Category = Electrical, one way I can think of using T-SQL is: > > > > > > Select A.Rec_Id From Table_Header A, Table_Header B > > > Where A.Rec_Id = B.Rec_Id And > > > A.Param_Name = 'Product' And > > > A.Param_Value = 'TV' And > > > B.Param_Name = 'Category' And > > > B.Param_Value = 'Electrical' > > > > > > The Select statement above returns me results: 1 and 3. But it will grow to > > > be quite complicated if I have many parameters to filter in my select > > > statement. Any better way to handle this coding? > > > > > > Thanks > > > > > > > > > > > > > > > > > > > > > Thanks for the reply...
The concept of Relational Database can help me to deliver a better design to cope my dilemma? That's the only design I can think of to fulfill the customer requirement and it's a big challenge to me. The code could become very complicated but it's already a fact that I can't perform modification to table structure with any new parameter coming in. Any suggestion for me? Thanks again Show quote "CBretana" wrote: > In a way, you are in the dilemma which often results from trying to achieve > conflicting objectives... > > The entire concept of Relational Databases is, in one sense (I need to be > careful here) designed to allow you to "Structure" your data so that it > closely matches the real world data objects or abstractions which represent > those objects, in order to allow the kind of data "processing" (like > arbitrary filtering, sorting, etc. that you are trying to accomplish. Old > style (pre-Relational) Database systems were comparitively inadeguate at > these sorts of things, *because* the data was not stored in a structured > relational way. > > But you are *Trying* to store your data in an unstructured way to allow as > much flexibility in what kind of data is allowed in to the database. These > two objectives are fundamentally inconsistent, and that is one reason why you > are experiencing difficulties > > "LBT" wrote: > > > I think I have provided a confusing example. My mistake :) > > > > Actually the table structure is designed in such a way to store variety of > > data structure sent in by user. Say for example, user might send in "Rec_Id > > [1], Product [TV], Category [Electrical]", "Rec_Id [2], Product [Telephone], > > Category [Electrical]", etc. > > > > At later time, user can send in data structure with new add-in parameter(s) > > - "Rec_Id [3], Product [TV], Category [Electrical], Power [1000kW], Color > > [Black]". The challenge is I cannot change the table structure (this is the > > dynamic solution requested by customer) everytime new parameter is being > > introduced. With the table design, I can simply store "Power" into Param_Name > > and "1000kW" into Param_Value (no adding of column to table structure is > > allowed). > > > > So with this table design, the only way I can perform records filtering (the > > filtering parameters however will be fixed which are Product and Category in > > this case) is as stated in my post? I just wonder whether there is any better > > way to perform the query. > > > > In fact, I have to create and configure few reference/definition tables so > > that my application will be dynamic enough to cater for this requirement. But > > it will be lengthy to explain the entire situation. > > > > Thanks > > > > "CBretana" wrote: > > > > > 1. What objects are being stored in this table? It looks like a generic > > > System table - holding attribute bnames and values, for a database. I would > > > recommend you get a good book on Relational Data modeling, and take a quick > > > look at it. You might consider normalizing the data structure a bit.. > > > Instead of having a Param_Name column in a table, you might want to create a > > > Products Table and a Categorys Table, but I don;t know what exactly you are > > > doing so it's hard to tell.. > > > > > > "LBT" wrote: > > > > > > > Hi experts, > > > > > > > > I have a table which stores data in multi-rows basis for a particular > > > > record. The structure of the table is as exhibit: > > > > > > > > CREATE TABLE [dbo].[Table_Header] ( > > > > [Rec_Id] [numeric](18, 0) NOT NULL , > > > > [Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , > > > > [Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > > > > ) ON [PRIMARY] > > > > GO > > > > > > > > Example data is shown as follows: > > > > Rec_Id Param_Name Param_Value > > > > 1 Product TV > > > > 1 Category Electrical > > > > 2 Product Telephone > > > > 2 Category Electrical > > > > 3 Product TV > > > > 3 Category Electrical > > > > > > > > In case I would like to filter out those records (Rec_Id) where Product = TV > > > > and Category = Electrical, one way I can think of using T-SQL is: > > > > > > > > Select A.Rec_Id From Table_Header A, Table_Header B > > > > Where A.Rec_Id = B.Rec_Id And > > > > A.Param_Name = 'Product' And > > > > A.Param_Value = 'TV' And > > > > B.Param_Name = 'Category' And > > > > B.Param_Value = 'Electrical' > > > > > > > > The Select statement above returns me results: 1 and 3. But it will grow to > > > > be quite complicated if I have many parameters to filter in my select > > > > statement. Any better way to handle this coding? > > > > > > > > Thanks > > > > > > > > > > > > > > > > > > > > > > > > > > > > If the customer DEMANDS a design like this then they should understand
and accept its weaknesses with regard to data integrity, performance and increased complexity. However, I would always discuss the alternatives with them first. Change control for adding new attributes should be their friend not their enemy. If they are seeking a zero maintenance solution then tell them to forget it - of course no such thing exists - or at least when a legacy application reaches that stage of maturity then it's probably already in need of replacement. The reason that proper change control is required is that users generally make very poor database designers. If you allow users to decide the format of business data you will lose any integrity and future value therein. Do you really expect users to comprehenend and analyze functional dependencies and standardise and conform attributes in the system? If they were capable of that then they must be database pros in which case they wouldn't need this kind of kludge. Corporate data is too important to be left to users to manage. -- David Portas SQL Server MVP -- This design flaw is called the EAV model; it fails in one year or less
in production. For fun, try to: 1) Add check constraints and default 2) Add a PK-FK constraint 3) write a query with a simple GROUP BY clause -- Thomas Coleman posted one of those for an another EAV -- total nightmare. EAV?
AMB Show quote "--CELKO--" wrote: > This design flaw is called the EAV model; it fails in one year or less > in production. > > For fun, try to: > > 1) Add check constraints and default > 2) Add a PK-FK constraint > 3) write a query with a simple GROUP BY clause -- Thomas Coleman posted > one of those for an another EAV -- total nightmare. > > EAV = Entity-Attribute-Value. It is basically a totally unstructured form of
design that allows you to make changes at run time. Contrary to Mr. Celko's opinion, there are a few **rare** circumstances where it makes sense. For example, I helped a company build a contract management system a couple of years that was then sold to international film distributors. On any given contract there are a series of attributes that are common to all film contracts (start date, expire date, etc). However, each company also had a bag of additional properties that they simply wanted to "track". In this case, "track" means store a value and see it in a list on a report. This data had no operations performed against it (summations, averages etc) and no complex reporting requirements (crosstabs and such). For this small, select circumstance, a microcosmic EAV design worked well. When a client decided that a given value was important enough to perform operations against or was need in complex reporting, I (as the designer) required that it then be spec'd out as a true attribute (column) in an appropriate normalized design. This provided a great deal of flexibility in a couple of ways. Firstly, it provided a means by which people could start tracking this data until a new version with a structured design was released. When the new version was release, part of this company's service was to migrate their existing unstructured data into the new structure. Secondly, many times people stated they wanted to track information that they really did not care about in the long run. Thirdly, the data that should be tracked differed greatly from customer to customer. A key component to this solution is that customers could not define new attributes. Rather, it required someone from the company for whom I built the system to define the attributes. To the user, they simply saw a list of attribute choices in a drop list. All that said, I would recommend against the EAV model and would work hard against its use. The moment you need to create sophisticated reports against it is the moment you realize how painful the design is. Thomas Show quote "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message news:534CB8E6-B209-41F3-A1E8-F4F1CED42C08@microsoft.com... > EAV? > > > AMB > > > "--CELKO--" wrote: > >> This design flaw is called the EAV model; it fails in one year or less >> in production. >> >> For fun, try to: >> >> 1) Add check constraints and default >> 2) Add a PK-FK constraint >> 3) write a query with a simple GROUP BY clause -- Thomas Coleman posted >> one of those for an another EAV -- total nightmare. >> >> Thanks.
AMB Show quote "Thomas Coleman" wrote: > EAV = Entity-Attribute-Value. It is basically a totally unstructured form of > design that allows you to make changes at run time. > > Contrary to Mr. Celko's opinion, there are a few **rare** circumstances where it > makes sense. For example, I helped a company build a contract management system > a couple of years that was then sold to international film distributors. On any > given contract there are a series of attributes that are common to all film > contracts (start date, expire date, etc). However, each company also had a bag > of additional properties that they simply wanted to "track". In this case, > "track" means store a value and see it in a list on a report. This data had no > operations performed against it (summations, averages etc) and no complex > reporting requirements (crosstabs and such). For this small, select > circumstance, a microcosmic EAV design worked well. When a client decided that a > given value was important enough to perform operations against or was need in > complex reporting, I (as the designer) required that it then be spec'd out as a > true attribute (column) in an appropriate normalized design. > > This provided a great deal of flexibility in a couple of ways. Firstly, it > provided a means by which people could start tracking this data until a new > version with a structured design was released. When the new version was release, > part of this company's service was to migrate their existing unstructured data > into the new structure. Secondly, many times people stated they wanted to track > information that they really did not care about in the long run. Thirdly, the > data that should be tracked differed greatly from customer to customer. A key > component to this solution is that customers could not define new attributes. > Rather, it required someone from the company for whom I built the system to > define the attributes. To the user, they simply saw a list of attribute choices > in a drop list. > > All that said, I would recommend against the EAV model and would work hard > against its use. The moment you need to create sophisticated reports against it > is the moment you realize how painful the design is. > > > > Thomas > > > "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message > news:534CB8E6-B209-41F3-A1E8-F4F1CED42C08@microsoft.com... > > EAV? > > > > > > AMB > > > > > > "--CELKO--" wrote: > > > >> This design flaw is called the EAV model; it fails in one year or less > >> in production. > >> > >> For fun, try to: > >> > >> 1) Add check constraints and default > >> 2) Add a PK-FK constraint > >> 3) write a query with a simple GROUP BY clause -- Thomas Coleman posted > >> one of those for an another EAV -- total nightmare. > >> > >> > > > > 3) write a query with a simple GROUP BY clause -- Thomas Coleman posted Hey now. I'll I did was solve a problem. I didn't endorse the design. :->> one of those for an another EAV -- total nightmare. Thomas That depends on what your role is in this project. Are you the Developer?
the Database Designer / Administrator ? The architect ? or the Technical Manager? If ANY of the former, I would get a book ot two on the subject and start reading.. The internet has loads of information on this -- google for "Relational Database design", "Database Normalization", "Database Normal Forms", etc.... If you are the technical manager, then You should Hire someone (on short-term contract if necessary), who know relational database design, (Get someone else who is demonstrably knowledgeable to technically interview them for you.) and then trust them to do the design for you.. Charly Show quote "LBT" wrote: > Thanks for the reply... > > The concept of Relational Database can help me to deliver a better design to > cope my dilemma? That's the only design I can think of to fulfill the > customer requirement and it's a big challenge to me. The code could become > very complicated but it's already a fact that I can't perform modification to > table structure with any new parameter coming in. Any suggestion for me? > > Thanks again > > "CBretana" wrote: > > > In a way, you are in the dilemma which often results from trying to achieve > > conflicting objectives... > > > > The entire concept of Relational Databases is, in one sense (I need to be > > careful here) designed to allow you to "Structure" your data so that it > > closely matches the real world data objects or abstractions which represent > > those objects, in order to allow the kind of data "processing" (like > > arbitrary filtering, sorting, etc. that you are trying to accomplish. Old > > style (pre-Relational) Database systems were comparitively inadeguate at > > these sorts of things, *because* the data was not stored in a structured > > relational way. > > > > But you are *Trying* to store your data in an unstructured way to allow as > > much flexibility in what kind of data is allowed in to the database. These > > two objectives are fundamentally inconsistent, and that is one reason why you > > are experiencing difficulties > > > > "LBT" wrote: > > > > > I think I have provided a confusing example. My mistake :) > > > > > > Actually the table structure is designed in such a way to store variety of > > > data structure sent in by user. Say for example, user might send in "Rec_Id > > > [1], Product [TV], Category [Electrical]", "Rec_Id [2], Product [Telephone], > > > Category [Electrical]", etc. > > > > > > At later time, user can send in data structure with new add-in parameter(s) > > > - "Rec_Id [3], Product [TV], Category [Electrical], Power [1000kW], Color > > > [Black]". The challenge is I cannot change the table structure (this is the > > > dynamic solution requested by customer) everytime new parameter is being > > > introduced. With the table design, I can simply store "Power" into Param_Name > > > and "1000kW" into Param_Value (no adding of column to table structure is > > > allowed). > > > > > > So with this table design, the only way I can perform records filtering (the > > > filtering parameters however will be fixed which are Product and Category in > > > this case) is as stated in my post? I just wonder whether there is any better > > > way to perform the query. > > > > > > In fact, I have to create and configure few reference/definition tables so > > > that my application will be dynamic enough to cater for this requirement. But > > > it will be lengthy to explain the entire situation. > > > > > > Thanks > > > > > > "CBretana" wrote: > > > > > > > 1. What objects are being stored in this table? It looks like a generic > > > > System table - holding attribute bnames and values, for a database. I would > > > > recommend you get a good book on Relational Data modeling, and take a quick > > > > look at it. You might consider normalizing the data structure a bit.. > > > > Instead of having a Param_Name column in a table, you might want to create a > > > > Products Table and a Categorys Table, but I don;t know what exactly you are > > > > doing so it's hard to tell.. > > > > > > > > "LBT" wrote: > > > > > > > > > Hi experts, > > > > > > > > > > I have a table which stores data in multi-rows basis for a particular > > > > > record. The structure of the table is as exhibit: > > > > > > > > > > CREATE TABLE [dbo].[Table_Header] ( > > > > > [Rec_Id] [numeric](18, 0) NOT NULL , > > > > > [Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , > > > > > [Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > > > > > ) ON [PRIMARY] > > > > > GO > > > > > > > > > > Example data is shown as follows: > > > > > Rec_Id Param_Name Param_Value > > > > > 1 Product TV > > > > > 1 Category Electrical > > > > > 2 Product Telephone > > > > > 2 Category Electrical > > > > > 3 Product TV > > > > > 3 Category Electrical > > > > > > > > > > In case I would like to filter out those records (Rec_Id) where Product = TV > > > > > and Category = Electrical, one way I can think of using T-SQL is: > > > > > > > > > > Select A.Rec_Id From Table_Header A, Table_Header B > > > > > Where A.Rec_Id = B.Rec_Id And > > > > > A.Param_Name = 'Product' And > > > > > A.Param_Value = 'TV' And > > > > > B.Param_Name = 'Category' And > > > > > B.Param_Value = 'Electrical' > > > > > > > > > > The Select statement above returns me results: 1 and 3. But it will grow to > > > > > be quite complicated if I have many parameters to filter in my select > > > > > statement. Any better way to handle this coding? > > > > > > > > > > Thanks > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >> The internet has loads of information on this -- google for "Relational Except that some of such online loads are simply compiled or plagiarized by >> Database design", "Database Normalization", "Database Normal Forms", >> etc.... folks with little or no grasp of the subject -- Anith  |
|||||||||||||||||||||||
Other interesting topics