Help with product table overhaul

Question

Help with product table overhaul

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

paul.goldstraw SSCrazy Points: 2626 More actions · Answer 1

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

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 2

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.....

paul.goldstraw SSCrazy Points: 2626 More actions · Answer 3

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

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 4

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