Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Optimal architecture for property management Expand / Collapse
Author
Message
Posted Sunday, November 04, 2007 9:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 08, 2008 1:55 AM
Points: 4, Visits: 8
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!
Post #418305
Posted Sunday, November 04, 2007 8:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:24 AM
Points: 32,781, Visits: 14,942
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #418360
Posted Monday, November 05, 2007 1:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 1:27 AM
Points: 2,366, Visits: 1,837
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"
Post #418399
Posted Monday, November 05, 2007 9:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 08, 2008 1:55 AM
Points: 4, Visits: 8
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?

Post #418632
Posted Monday, November 05, 2007 11:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:24 AM
Points: 32,781, Visits: 14,942
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #418698
Posted Monday, November 05, 2007 12:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:44 PM
Points: 7,084, Visits: 14,684
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?
Post #418702
Posted Monday, November 05, 2007 1:21 PM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: 2 days ago @ 3:54 PM
Points: 8,369, Visits: 733
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.



Post #418742
Posted Monday, November 05, 2007 2:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 08, 2008 1:55 AM
Points: 4, Visits: 8
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
Post #418758
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse