May 2, 2008 at 4:04 am
Hi
I was wondering if anybody could give me some advice with the following:
I have a table called Product (this is for an e-commerce site), now I need to show this table in UNF and use repeating groups. The common attributes of Product are ProdID, Brand, Model, UnitPrice. But then a product (say of type laptop) will also have attributes of CPU, Screensize, OS, etc. But I may also have a product (of type Camera) which will have attributes of MegaPixels, Zoom, Bluetooth, etc.
My question is how do I use a repeating group to display the product type specific attributes/columns?
Thanks in advanced
Jeff
July 26, 2008 at 11:18 pm
Hi
I was wondering if anybody could give me some advice with the following:
I have a table called Product (this is for an e-commerce site), now I need to show this table in UNF and use repeating groups. The common attributes of Product are ProdID, Brand, Model, UnitPrice. But then a product (say of type laptop) will also have attributes of CPU, Screensize, OS, etc. But I may also have a product (of type Camera) which will have attributes of MegaPixels, Zoom, Bluetooth, etc.
My question is how do I use a repeating group to display the product type specific attributes/columns?
Thanks in advanced
Your problem is : your products have their own set of attributes which are quite different to the others and you can't make a generic table to hold all of your products' details.
How about this table schema desgin :
Product
ProdID Brand Model UnitPrice ProductType
1001 ABC M901 3000 L
1002 CDE T456 3200 L
1003 XYZ A900 5000 C
ProductType
TypeCode TypeDesc TypeDetailTable
L Laptop TDT_Laptop
C Camera TDT_Camera
TDT_Laptop
ProdID Brand Mode CPU Screensize OS
1001 ABC M901 xx ......................
1001 ABC M902 xx ......................
1002 CDE T456 xx ......................
1002 xsw T457 xx ......................
TDT_Camera
ProdID Brand Mode MegaPixels Zoom Bluetooth
1003 XYZ A900
1004 333 ccc
You can even enter some models into their 'TypeDetailTable' before you sell it, eg. 1002 XSW and 1004 333.
July 26, 2008 at 11:19 pm
Here is my updated version, ignore the previous one.
How about this table schema desgin :
Product
ProdID Brand Model UnitPrice ProductType
1001 ABC M901 3000 L
1002 CDE T456 3200 L
1003 XYZ A900 5000 C
ProductType
TypeCode TypeDesc TypeDetailTable
L Laptop TDT_Laptop
C Camera TDT_Camera
TDT_Laptop
ProdID Brand Mode CPU Screensize OS
1001 ABC M901 xx ......................
1001 ABC M902 xx ......................
1002 CDE T456 xx ......................
1002 xsw T457 xx ......................
TDT_Camera
ProdID Brand Mode MegaPixels Zoom Bluetooth
1003 XYZ A900
1004 333 ccc
You can even enter some models into their 'TypeDetailTable' before you sell it, eg. 1002 XSW and 1004 333.
July 26, 2008 at 11:21 pm
sorry, forgot to include this small part :
Your problem is : your products have their own set of attributes which are quite different to the others and you can't make a generic table to hold all of your products' details.
July 28, 2008 at 7:40 am
To normalize this kind of scenario, it may be more practical to have an attribute table, where each record contains an attribute type and an attribute value. The intermediate table would match a product type to as many atttribute types as exist for that product type. The original product table would be fine as is. Your thoughts?
Steve
(aka smunson)
:):):)
Jeffrey Hiscox (5/2/2008)
HiI was wondering if anybody could give me some advice with the following:
I have a table called Product (this is for an e-commerce site), now I need to show this table in UNF and use repeating groups. The common attributes of Product are ProdID, Brand, Model, UnitPrice. But then a product (say of type laptop) will also have attributes of CPU, Screensize, OS, etc. But I may also have a product (of type Camera) which will have attributes of MegaPixels, Zoom, Bluetooth, etc.
My question is how do I use a repeating group to display the product type specific attributes/columns?
Thanks in advanced
Jeff
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 28, 2008 at 10:00 am
Hi Jeffrey;
Beware of any design advice you get given the lack of information (i.e., conceptual model, requirements, etc.) given in your first post. I also advise you to steer clear of an EAV design (which appears to be what 'smunson' is suggesting).
The question, to me, however, looks more like a question on how to formulate a particular query. If that's the case, see http://www.sqlservercentral.com/articles/Best+Practices/61537/.
If this is still an open design project (I notice your OP is a bit old), the best suggestion I can offer is to contract for a data management professional to work with you on the design, or, if you have time, obtain some training in data management fundamentals.
HTH,
TroyK
July 28, 2008 at 10:17 am
Would you mind being a bit more specific? If my design is "EAV", and that's a problem generally, then please indicate why, and tell me what "EAV" stands for. If you're going to teach a newbie something, then at least do so with details, logic, and reason, rather than a mere statement about avoiding something where they may have no idea what it is you're asking them to avoid or why. Also, you might take note that I stated my idea "may" be more practical, and solicited the OP's input to facilitate discussion on the topic.
I don't mind learning that my idea isn't a good one, but I'd prefer to do so because I was "shown" the error of my ways rather than simply told it was a bad idea...
To facilitate discussion, here are some reasons why I think my design can work:
1.) Eliminates the need for a complex data structure that might not be easily adapted to new items being sold in an e-commerce structure where their unique attributes effectively create a need to completely redesign the solution.
2.) It provides the grouping capability requested by the OP.
3.) It's flexible, and properly imposes the burden of ensuring proper data linkage on the administrative interface, where it rightly belongs.
Your thoughts?
Steve
(aka smunson)
:):):)
cs_troyk (7/28/2008)
Hi Jeffrey;Beware of any design advice you get given the lack of information (i.e., conceptual model, requirements, etc.) given in your first post. I also advise you to steer clear of an EAV design (which appears to be what 'smunson' is suggesting).
The question, to me, however, looks more like a question on how to formulate a particular query. If that's the case, see http://www.sqlservercentral.com/articles/Best+Practices/61537/.
If this is still an open design project (I notice your OP is a bit old), the best suggestion I can offer is to contract for a data management professional to work with you on the design, or, if you have time, obtain some training in data management fundamentals.
HTH,
TroyK
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 28, 2008 at 11:18 am
Hi Steve;
I want to preface my remarks by saying that I think it's admirable to want to help someone out with a question they have, and I appreciate that people like yourself make an effort to do so. I just think that sometimes the advice I see can do more harm than good.
You are correct, though, to seek more information from the OP in order to provide a better answer. Since you are newer to db design and haven't run across EAV as an antipattern, here's a link to get you started:
http://oracle-wtf.blogspot.com/2006/02/eav-returns-concrete-elephant-approach.html
An internet search for "EAV antipattern", "EAV Celko", and "EAV SQL" will turn up some others. You will even find some articles on this site regarding the design. My take is that the discussion on the articles is more valuable than the articles themselves.
You are likely to find people advocating EAV as a good design idea in the clinical research realm. My opinion is that they're wrong, though. There are better ways to approach design for flexibility.
I'm afraid that to go much more into detail requires something more than can be accomplished via discussion board posting. I'll simply close with the same advice I had for the OP -- obtain a thorough education in data management and db design fundamentals. Here's my ego-link on the topic:
http://www.sqlservercentral.com/articles/Data+Modeling/61817/.
HTH,
TroyK
July 28, 2008 at 11:59 am
TroyK,
I appreciate you providing some links - unfortunately, neither one provides ANY level of detail on what's wrong with an EAV design. All I got there were statements like "it won't work". Frankly, that's not a viable argument. What I'm looking for are technical reasons related to performance or the data model itself that render it a bad idea. Given the "set-based" nature of the concept, I'm having a hard time coming up with a scenario in which performance is a problem that's caused by the data model instead of hardware limitations. Given the kind of time any serious web search entails, I'm going to have to let the OP do the research on what problems might exist with EAV. I was hoping you could at least describe a "how it becomes a problem" scenario that has something to do with the data model as opposed to the administrative requirements of using said model. That way, it would be easier to see your version of the "bigger picture", if you will.
The reason I'm remaining skeptical for now is because I've seen my share of obfuscation over the last 30 years about why some thing or other "wouldn't work", and on so many occasions, there was never any substance to the why. Implementation after implementation has succeeded despite the best efforts of detractors, and I have yet to come across a similar scenario where I could shake loose the technical detail. Please pardon my cynicism, but I've "been there, done that" too many times. I've created data models for things for a lot of years, so if one of my many design tools is somehow deficient, I need to know the details on why and under what circumstances, without having to resort to a 100 page treatise on the subject. If it requires that level of discussion, then the problem probably isn't the data model, and is more likely a concern over how it's used. Certainly a valid concern, but that concern applies just as equally to ANY use of a computer in a business, so that's hardly sufficient reason to reject a given data model. If I thought there were more peril than benefit in such a data model, I wouldn't use it, and to date, I don't have any viable data to suggest that's the case. Do you have any data for a "properly implemented" EAV data model that can demonstrate your perspective?
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 28, 2008 at 5:02 pm
Hi Steve;
Here's a link to a more detailed account of the problems one can run into with an EAV design: http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/
In a nutshell, expect:
1) Difficult or impossible to declare constraints
2) Difficult to understand physical model
3) Order-of-magnitude slower bulk load times for informationally equivalent data
4) Except in very specific cases, much worse query performance.
As an exercise, you might try taking a well-designed table that represents a particular entity and re-working it using an EAV approach. Then try some queries against it that utilize multiple logical attributes of the particular entity and compare the complexity of the query formulation and the query plans produced by the optimizer. What you see is just the tip of the iceberg in terms of problems caused by this approach.
HTH,
TroyK
July 28, 2008 at 8:29 pm
cs_troyk (7/28/2008)
Hi Steve;Here's a link to a more detailed account of the problems one can run into with an EAV design: http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/
In a nutshell, expect:
1) Difficult or impossible to declare constraints
2) Difficult to understand physical model
3) Order-of-magnitude slower bulk load times for informationally equivalent data
4) Except in very specific cases, much worse query performance.
As an exercise, you might try taking a well-designed table that represents a particular entity and re-working it using an EAV approach. Then try some queries against it that utilize multiple logical attributes of the particular entity and compare the complexity of the query formulation and the query plans produced by the optimizer. What you see is just the tip of the iceberg in terms of problems caused by this approach.
HTH,
TroyK
Not to mention the inability to data type anything. It's a huge violation or the relational model, and arguably RDBMS functionality in general (since constraints can't be enforced anywhere but in app code).
It can be a true nightmare if used for anything other than HIGHLY episodic variable pulls. It's a bit like the XML data type - it's great for referencing "occasional" data, (stuff you might need access to once or twice a year, in a report, with no analysis), stuff you might not have planned for/foreseen, etc... Other than that - EAV has a strong tendency to start sucking the life and the storage space out of your server.
Actually - I shouldn't even drag XML into this, since XML CAN be typed, CAN be indexed efficiently, etc.... Either one over-used will have bad consequences on your perf, though.
And yes - I suffered cruelly after taking over a system with heavy duty EAV usage. The big problem is that users that use it don't know "when to stop" and when to ask for appropriately formatted data. Why bother to ask for appropriately formatted stuff, when EVERYTHING can be represented in this way? and then you start edgin toward the rather extreme version that blog entry Troy mentioned....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 29, 2008 at 1:13 am
Thanks for all the help guys unfortunately I have already done the design as it had to be done by mid May, but I will definitely be reading up on some of the topics and links suggested in your answers.
Thanks again
July 29, 2008 at 7:10 am
TroyK,
I appreciated the story, and it was definitely a good read, but going from "EAV" to anything even remotely resembling what that story described is a rather vast leap. That story would have to qualify as the very worst possible example of gross incompetence of the business leaders who bought the solution. Surely you can't expect me to believe that the "EAV" solution I described (with multiple tables, by the way) is somehow the same thing. It has some similiarity, but merely going in the general direction of metadata does not automatically mean you avoid it on that basis alone. Sorry, but I think we'll just have to agree to disagree. I'm of the opinion that one scary story has inappropriately affected your judgement.
I've found that over the years, making businesses properly manage their data has benefits that are worth far more to that business in the long run than any perils inherent in their data model. Such "forced behavior" tends to produce better overall management of IT infrastructure, as the business learns how to align it's IT functionality with it's business processes.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 29, 2008 at 10:04 am
smunson (7/29/2008)
TroyK,I appreciated the story, and it was definitely a good read, but going from "EAV" to anything even remotely resembling what that story described is a rather vast leap. That story would have to qualify as the very worst possible example of gross incompetence of the business leaders who bought the solution. Surely you can't expect me to believe that the "EAV" solution I described (with multiple tables, by the way) is somehow the same thing. It has some similiarity, but merely going in the general direction of metadata does not automatically mean you avoid it on that basis alone. Sorry, but I think we'll just have to agree to disagree. I'm of the opinion that one scary story has inappropriately affected your judgement.
Nope. Actually, my objective testing, experience, and extensive study of the relational model are what have affected my judgement.
Listen... if you think that you can get equal or better performance from an EAV design over the equivalent "regular" design, I invite you to produce tests and publish your benchmarks. If you have deployed an EAV design into production that has not, over the years, become a hodgepodge of mismanaged and difficult-to-decipher junk heaps, you should definitely publish a case study, if at least to demonstrate one of the few instances where the EAV approach was the proper way to go.
I've found that over the years, making businesses properly manage their data has benefits that are worth far more to that business in the long run than any perils inherent in their data model. Such "forced behavior" tends to produce better overall management of IT infrastructure, as the business learns how to align it's IT functionality with it's business processes.
Steve
(aka smunson)
:):):)
This is definitely congruent with my philosophy. I suppose the difference lies in what it means to "properly manage their data".
Best,
TroyK
July 29, 2008 at 11:45 am
Perhaps one of our differences relates to when to use the Entity, Attribute, Value approach. There's never been a scenario where that was the entire solution - it was always in place solely as a rather small part of a whole, and most commonly in pieces of manufacturing or e-commerce, where the overall number of records for entities with attributes and values is at most in the few tens of thousands. Constraints in those systems were ALWAYS in the application layer where they belong, and an administrative interface for the data management of all "metadata" was always the first order of business in the development process. Also, these systems were for relatively small companies, so there just wasn't a need to concern oneself with years of mismanagement and neglect that one might otherwise have with large companies. As these databases were an integral part of how they did business, a LOT of attention was focused on them.
Given the number of years that have passed (at least 7 yrs now), most of these systems have probably since been replaced. If I still had any access to those systems, I might have the content for a paper, but I wouldn't have the time. As with any method or solution, "EAV" worked for those companies at that time, and for a rather surprising number of years, given the contract nature of the development. In several cases, the plan was to use the system just long enough to allow those companies to get to a position where their revenue would be sufficient to justify a higher-end solution - typically 6 to 18 months down the road. None of those companies with such plans had needed to replace the systems despite being at least 4 years down the road and with sufficient revenue to go significantly higher-end. I've kept contact with most of those folks, and their conversions to new systems from old were reported to me with comments like "our vendor was suprised at how easy the conversion would be", and other similar statements.
So, my experience tells me that "EAV" does work, as long as you understand where, when, and how to use it, and recognize the limitations. If you want to try and use "EAV" with millions of records, then yes, I can see the potential for problems - especially if we're talking about a large corporate environment where alignment of IT to business processes is minimal. However, I really don't think that large corporate environments lend themselves to the scenarios where an "EAV" approach is useful. They never seem to want to manage data.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply