Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Optimal architecture for property management


Optimal architecture for property management

Author
Message
tom-539396
tom-539396
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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!
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: Administrators
Points: 40940 Visits: 18862
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
My Blog: www.voiceofthedba.com
ChiragNS
ChiragNS
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2597 Visits: 1865
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"
tom-539396
tom-539396
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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?
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: Administrators
Points: 40940 Visits: 18862
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
My Blog: www.voiceofthedba.com
Matt Miller (#4)
Matt Miller (#4)
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8332 Visits: 18268
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?
Antares686
Antares686
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: Moderators
Points: 8712 Visits: 780
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.



tom-539396
tom-539396
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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!

Smile

Thanks guys!!!
Tom
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search