Hi,

Posting in "my" own Forum....

Converting an old Options Hierarchy system.

I'm wondering which design is better.

Solution 1 :

Basically Table A relates to Table B through these possible keys

- In Table A :

cKey1 = Field A & Field B

cKey2 = Field A & Field B & Field C

cKey3 = Field A & Field B & Field C & Field D

- In Table B : cHierarchyOptionKey = Field A & Field B & Field C & Field D

Each record in Table B has a unique separated combination stored in Table B

Record#1 - Field A:A10 | Field B:B10 | Field C:Empty | Field D:Empty |

Record#2 - Field A:A10 | Field B:B10 | Field C:C10 | Field D:Empty |

Record#3 - Field A:A10 | Field B:B10 | Field C:C10 | Field D:D10 |

Solution 2 :

Table A relates to Table B (2 options) and Table C (3 options) and Table D (4 options) through 3 of these new FM7 concanation relationships.

Table B, C , and D are all related through mirrored TO's so that one single portal line can hold all 4 combinations

The first design allow for Tree-View but somehow breaks the Relational rule.

I assume the second design, more 7 oriented is cleaner, even if it doesn't allow for indented views.

But apart this, would it be for sure more effective, and then why ?

Thanks for any input.

Ugo: I'm not sure I'm understanding you correctly, but it looks interesting. Are you saying that a user is in Table A and makes a choice among three keys? Then based on the choice (and using Solution 2) he goes to Table B if he chooses cKey1, to Table C if chooses cKey2, etc. I certainly prefer v7 over previous versions, so Solution 2 would probably get my vote, but maybe you could provide us with a concrete example of an Options Hierarchy System to clarify this situation.

Hi,

Thanks for answering.

Ok,

First of all let me say I tested both solutions, and they both seem reliable.

It's for a Flexible Pricing System I'm building.

Standard SetUp :

10 Price Grids exist, each giving a set of Standard Discounts for a given list of Manufacturers.

Customer with a GridID0002 has:

->5% rebates for ManufacturerA products

->10% for ManufacturerB

->...

Customer with a GridID0003 has :

-> 10% rebates for ManufacturerA products

-> 15% for ManufacturerB

-> ...

Custom SetUp :

Bonus Discounts are offered for a subset of my Catalog Records.

So that :

Customer with a GridID0002 has :

-> 5% rebates for ManufacturerA products

-->10% for Category X from ManufacturerA products

->10% for ManufacturerB

--> 15% for ProductID0008 from Category Y from ManufacturerB records

---> 20% for CustomerID0005 for ProductID0008 from Category Y from ManufacturerB records.

When entering items in the Line Items, the correct lookup discount should be looked up based on the compound key.

If I rely only on the FM7 concatanated links in the OneTable solution, I would always lookup the first record with 2 keys assembled, even if my entered item correspond to a Level4 Hierarchy Discount (ManufacturerID & Category & ProductID & CustomerID).

So I reverted to 4 good old Compoundkeys instead.

The other solution, the Multiple Discount Tables (I mean Tables) tied to the Line Item with FM7 concatanated keys is working fine.

But the problem is when in the Customer record. I can't have all Discounts listed at once. I need 4 to 6 portals to get all listed rebates, and so it is for the Customer Discount Sheet which is send to the Customer.

These are only a few examples of my complex price List, which also account for Quantity Breaks, Promotional Items, Special Prices,etc.

Just wanted to simplify the job a bit...