November 4, 2007 at 9:28 am
I am developing an online catalog management system and have myself tied up in knots on how best to implement a varied array of properties for catalog products. My initial approach was to stay generic by providing a user definable set of properties. Then because of the semi-hierarchical nature of them I recoiled and started to brute force it with specific tables for each property type. There must be some way to structure this that is the best practice for both property management and property search.
Let me be more concrete. In the swimwear industry a bikini top may have the following attributes:
Style (Halter, Triangle, Bandeau...)
Fabric Type (Lycra/Spandex Blend...)
Prints (White Orchid, Parlor, Blue Sky...)
Print Types (Flower, Stripes, Solids...)
Chest Size (32, 34, 36 and S, M, L ...)
Cup Size (A, B, C...)
I need to accomplish a couple things.
First I need to generate SKUs for each inventory item based on a root product. Any one top would be exploded out into a SKU of or some such inventory trackable form.
Second I need to be able to materialize the products on the site via any of the attributes (Style+Chest Size+Print Type).
I'm looking for guidance on how best to do this. XML Properties attached to each SKU? Tables with each property set represented? Generic custom property tables with prop id's and separte tables for each data type?
Anyone that may have some experience here please feel free to share opinions and/or point me to some information to help out.
Thanks so much!
November 4, 2007 at 8:11 pm
I haven't done property management, but I did some work with education and we had a very free slowing type of architecture as well.
We used to handle this two ways. If we had fairly well defined attributes, especially for searching, they'd be put into a table as columns. If they didn't necessarily apply to enough places, then we'd make them defined attributes. That would balance the search needs with keeping things organized for data management.
November 5, 2007 at 1:40 am
Agree with steve there. If your atributes are least like to change then you can have them as columns. Otherwise you can keep then user definable. Keeping them user definable will provide the maximum flexibility as regards data changes.
"Keep Trying"
November 5, 2007 at 9:21 am
Part of the issue here is how do you deal with attributes for different categories of products. For example my above swimwear example applies, but then throw in Jewelry, Footwear, Bags, etc... Each having their own array of properties that are specific to the category. And then add to that the fact that I would like to pull the products using attribute terms (e.g. "Tops + White Orchid").
This might sound unusual, but I'm thinking that using Full Text searching would be ideal. If during product data entry all attributes were selected that applied to a given item, then I could stuff all of those into a table with Full Text searching defined and do all extraction from there.
Does that make sense?
November 5, 2007 at 11:51 am
That does make some sense, but I think you could get lots of common attributes in properties. Searching could be easier if you stored all the attributes in an XML document, indexed it, created separate elements for each item, etc. However I don't have experience here.
I think in any case, FTS or XML, if you're ad hoc searching, it will run relatively slow.
November 5, 2007 at 12:09 pm
I think Steve is giving you the right way to "derive" the ultimate structure. Meaning - you may need to do this iteratively, while you determine all of the meaningful elements.
For "loosely"-structured, or fluid data structures, the XML type is ideal. That's what the 2005 team built it for. The advice put forth on the matter is to try to take a crack at figuring out what elements get a LOT of traffic/search/filter/select activity, and split them out of the XML into "real" data structures. If you find you need more specificity on something than what is physically defined, use your XML schema to approximate your way into the right fields, and once you have it right, then consider whether they need to be moved into the "real" structure.
I've successfully use this method when I just can't get a decently read on what might be "once in a blue moon" data, and what might get a lot more traffic.
Don't forget to INDEX your XML data. SQL2005 allows for some pretty darn good performance if you index things correctly, and XML datatypes are indexable as well. For occasional querying, you can get some fairly nice performance, with a primary XML index and possible another secondary one on the "heavier" nodes.
----------------------------------------------------------------------------------
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?
November 5, 2007 at 1:21 pm
Lot's of ways to do this sort of thing so you have to kind of break down your concepts of what you are addressing.
For example you know that you ultimately start with an overall category of information such as lingerie may be even higher level such as clothing. Consider how to
You also have fixed conditions such as a specific product, it's sku, it's upc which determine price, this is irregardless of the overall description of the product. So the question is how to most effectively describe the attributes of a product so that you can find products with descriptions matching that availability. You also have to consider how sotkc relates to the product plus the attributes, you don't want to say you have a halter top wth floral roses on it with a 36C when you don't have the specific product.
You just have to break it down logically and keep working on it until you cover potential holes in the design that would allow relational flaws to be injected.
I think I might start with a couple of tables like so
Products - Base product, UPC, SKU information, and providing vendor.
Vendors - Information about the vendor.
Categories - Category of products, this might have an ultimate set of root product categories such as food and clothing or maybe lingerie, pants, etc. Might use some heirarchy of parent child in here.
Product_By_Category - Products associated with the lowest child it would relate to in Categories. I make seperate because you might have multiple Categories a product fits in for the sake of finding.
Attributes - Individual descriptors such as product, attribute type, attribute value (Things Like A, B C, S, M, L, 10, 20, 30, which are an attribute fact, this might even dervie from some other tables information for controlled options).
Attrbiute_Types - Things like Style, Fabric Type, Prints, Print Types, Chest Size, Cup Size
But even here I can think of things I could possibly do and once I decide I would refator the design several times to be sure I am happy with it.
November 5, 2007 at 2:00 pm
Thanks so much for taking the time to respond. What you have described here is very similar to what I originally designed, before I started looking into the million different ways it can be done. I think the biggest general guidline I'm getting from all responses is to basically try and stay away from FTS if possible, keep things within the table structure and index effectively. I do understand the intention behind the XML fields. It's all really cool, but I don't want to go there unless it's necessary. I think I can for the most part avoid it for now.
If anyone can get me a free box of Commerce Server, that may be the best solution!
🙂
Thanks guys!!!
Tom
October 29, 2020 at 9:25 am
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply