July 28, 2010 at 3:13 am
Hi,
With the structure of the forums as they are, it's a bit of a pain having to post in one particular forum because by and large this doesn't concern any particular version of SQL Server, though given the fact I am running 2005 and this subforum seems to have the most activity, hopefully I will get the broadest set of answers possible. So with that said, here's my problem
I co-develop a bespoke, internally developed ordering system for the company I work for. Obviously with an ordering system, you're going to have products, so there is a product table, and because a lot of our products are 'bundles', that is, they are collections of products, there is a separate table dealing with the parent/child relationships within these bundles. In its current implementation, by ordering the parent 'bundle', all the child products are ordered with it. This has been in place for several years, but I am now being asked to implement a great many new bundles, which isn't a problem in itself but it has got me thinking that there is no reason this couldn't just keep happening. 'Wouldn't it be nice' I think to myself 'if the bundling system was more dynamic? What if instead of adding new bundles every time they are requested, these bundles are just broken apart to their constituent parts and we allow the admin department who are entering the orders to build them as they need to?'
Then I think if I am going to make this change, there are others I could try to implement at the same time. Some of our products are made up of a number of sub-products, so at that stage we're getting into the realm of multi-tier bill of materials functionality. It would also be useful to have some element of validation so that the person keying the order in, while free to mix and match to a certain extent, cannot order completely incorrect combinations of items. Another complication would be pricing. With the current bundling system, if two of effectively the same item appear in two different bundles, it will appear twice in the product table with different prices which would based on the other factors like the length of time the product will be rented to the customer. If I now have that one product mixed with other products in the fashion I am thinking of, how do I ensure that the pricing is correct?
I know so far this is a bit vague so here's the basics.
- The orders can be of different types - rental, purchase, loan etc
- The orders can contain different products
- The orders can have some of the products branded differently
- The orders can have various rental lengths and various warranty levels
Given all that, trying to maintain a bundling system to cater for all the possibilities looks like it's going to become a harder and harder task. My initial thoughts on how i would tackle this concerned a set of matrix tables of what can be ordered. Obviously this has to be fairly loose or i'm just reinventing the wheel, but I thought having some restrictions in place. So for instance, certain products that can never be purchased are removed from the product list when the order type is purchase, or certain rental lengths are removed when they are incompatible with with the selected warranty. In this way I guide the user without making them hunt for the specific bundle they want.
I would appreciate any feedback or ideas anyone has about how they might go about a problem such as this. I know this is a pretty hefty post so if anyone has taken the time to read all the way through my ramblings, thank you.
Paul
August 3, 2010 at 3:03 am
No suggestions at all? Am I asking too much or not giving enough detail?
I have come up with a structure I think would work but I thought speaking with other people might weed out some of the potential pitfalls.
Thanks for looking
Paul
August 4, 2010 at 9:11 am
Hi Paul
lots of things to cover here....let make sure I get what you are looking for somehthing like the following maybe
Rental business
product qty time/bundle price
mixer 1 day 59.99
mixer 1 bundle 159.99
bundle could be a week with extra tools.
so may be you need 2 child table and one grandchild
Single items <--- Products ---->> Bundles --- >> Items that belong to the bundle
|
Hopefully this is in the right direction or maybe it help other to join in.....
August 4, 2010 at 10:02 am
Thank you 456789psw 🙂
Well our current system kind of works a bit like what you're describing. I am always wary of describing what we do where I work, but if i were to use a computer analogy, at the current time we have a system in place a little like this
--Product Table
ProductId Name MasterChild
1 Apple iMac 1 M
2 Motherboard C
3 Memory C
4 Mouse C
--MasterChildRelationship Table
MasterProductId ChildProductId
1 2
1 3
1 4
What this doesn't take account of is
- Motherboard is made up of Capacitors A, C and F
- Motherboard contains a 40mm fan
- 40mm fan is made up of a ball bearing, a rotor and a housing
- Memory is composed of Samsung 64Mbit DIMMs
It also means every time a new bundle comes along, I have to hand-code it. Ok if it's 1 new bundle, not so ok if it is 50 of them.
Suppose using the analogy above, I now have iMac 2, which uses new-fangled memory 'Memory 2'. In my current structure, I need to
- Add a new master bundle for iMac 2
- Add a new line for Memory 2
- Add in all the relationships again between iMac 2 the bundle and it's consitutent parts
What I am trying to do is this
-- Bundle Table
BundleId Name
1 iMac 1
2 iMac 2
-- Motherboard Table
MotherboardId Name
1 Motherboard
-- Memory Table
MemoryId Name
1 Memory
2 Memory 2
-- Mouse Table
MouseId Name
1 Mouse
And then with all of that semi-static data in place, I want to add in some rules tables
--Bundle_Motherboard_Link
BundleId MotherboardId
1 1
2 1
--Bundle_Memory_Link
BundleId MemoryId
1 1
2 2
--Bundle_Mouse_Link
BundleId MouseId
1 1
2 1
In this way I am giving the user more flexibility over what is valid with what.
In this simplified example I would understand if someone argued my new way is needlessly complex, however I have simplified it greatly in terms of columns to make it suitable for posting here. What this example doesn't easily show is that with each type of product in its own table, information about it, such as the number of DIMMs on the memory module, or the DPI of the mouse, or the socket format of the Motherboard can be stored. If i try to store all of that in the same table, I am storing information about the socket format of a mouse, and the DPI of a memory module, which obviously doesn't make any sense
So since I posted the OP, I have put together much of this structure. It isn't ideal, but it should afford me some more flexibility. Where I still want to improve and haven't yet is this idea of a multi-tier hierarchy. I want to be able to scan across multiple levels of detail and determine that
iMac 2 is made up of A, B and C
C is made up of D and E
E is made up of F, G, H and I
I is made up of J and K
B is made up of E and J
To do this I need a structure that allows for anywhere between no levels and n levels. At the moment each item can only be a master or a child, not both. I had hoped to determine some way of handling this within my new structure but I am still unsure how to proceed.
I hope I have explained clearly where I am and how things are currently working
Thanks
Paul
August 4, 2010 at 10:52 am
first in re guards to "needlessly complex" it must be this what my 1 st thought was just make
Details items table, with FK pointing to them.
That would give U performance issues as the table would be in 2nd normal form. This would cause
merge or hash joins.
The way you have choose is going to encourage loop joins!
-- also better for inserts less locking and blocking my guess the table could become VERY big this any other way.
Many Small Fast joins are better then 2 big table joins!
So far so good..
"At the moment each item can only be a master or a child, not both?"
I dont under stand on the "both" part? seems like you both already
in your example #2 is both a child of the iMac and a parent of 3"E" so it seems it is both?
1 iMac 2 is made up of A, B and C
2 C is made up of D and E
3 E is made up of F, G, H and I
4 I is made up of J and K
5 B is made up of E and J
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply