Implementing AND / OR Relationship 1

[rajkum]

Hi,

Table 1:
destinationCodeTable
{
destinationID
destinationNm
}


Table 2:
personHoliday
{
personID
destination???????
}

Example data set of destinationCodeTable is as follows:
Bahamas
Ontario
Tokyo
Angkor Vat
.
.


The destination in personHoliday could be defined as
Bahamas AND(Tokyo OR Angkor Vat) etc..

I have to relate the destination to the person.
What is the best way of implementing this...?


Thanks,
Roshan.

 

[amorous]

Your tables are not normalized... you can do a join between these tables using LIKE operator but thats not a good programming practice...

I suggest you to normalize your tables..

-VJ

 

[ESquared]

As long as PersonHoliday can have multiple records per person and destination is the destinationID from destinationCodeTable, then his tables look normalized to me. He's using codes not character fields.

[tt]Destinations
DID Name
1 Bahamas
2 Ontario
3 Tokyo
4 Angkor Vat

PersonHoliday
DID Group
1 1
2 2
3 2
4 2[/tt]

Each destination grouped together in a list of ORs has the same group number. Each destination that is in a new AND clause starts a new group.

Then in a query you just make sure there is at least one destination per group (unless you really meant XOR which would require another field to describe whether the ORs are exclusive or not).

 

[rajkum]

Thanks for your comments / answers.

But the problem I have is as follows:

A person is entitiled to a vacation. And the vacation destination would depend on the choices one makes.
e.g., A person can have two vacations.
If s(he) chooses Bhamas then s(he) has to choose the second destinations between Angkor Vat and Hongkong
Bhamas AND (Angkor Vat OR HongKong)

AND this definition could change any time.
I want to design the table in such a way that the above situation can be incorporated in the DB.
Also, I want the user to have the flexibility to change the rules any time.

I do not want to hardcode the rule in the application because the rule changes frequently.

IF it is just destination 1 AND destination 2
Then I would have two fields in PersonHoliday
PersonHoliday
(
personID
destination1ID
destination2ID
)
OR If it was more than two I could have multiple rows
in
PersonHoliday
(
personID
destinationID
)
But I am lost when the rules could be
destination1 AND (destinationX OR destinationY)
etc..

Any help would be really appreciated.
Thanks,
Roshan.

 

[TJRTech]

We had a similar need for a RULES engine in our database.

The RULES engine allowed us to place data in various tables, and then, compile the rule from the data in the tables to generate and store a SQL text statement that when executed generates results.

I think you could use the same approach.

For this we had the following tables:

1. RULE - PK is a RULE_ID. Gave a name for the rule and a long varchar string for the Compiled Rule text.

2. MATCHING_EXPRESSION - This table lists all the matching expression that are part of the rule. It has a FK into RULE and has an int ORDINAL column so one knows the "order" of the matching expressions within the rule. Each matching expression has: MATCHABLE_FIELD_ID fk (see next table def), OPERATOR_ID FK (see table def), MATCHING_CRITERIA varchar and optional (nullable) NEXT_OPERATOR_ID (for stringing togheter expressions), oh and ORDINAL (int). The MATCHING_EXPRESSIONs get strung together to generate the syntax of a compiled rule.

3. MATCHATABLE_FIELD table describes the datatype of a matchable field, the name of the field (usually the column name from a table) and a Table name. You want your rules to work against real tables and real column values from your db and it is the MATCHABLE_FIELD table that gives the list of tables and columns that can be used when constructing rules. This table typically holds static list you set up once and extend only when you change your DB model.

4. OPERATOR table provides the list of operators. All you need here is a NAME and a SYMBOL. (And AND, Or OR, Like LIKE, Equal =, Not Equal !=) are all tupples for this table. These are typically a static list you set up once. The name is used for display purposes and the SYMBOL for construction of the SQL text.

5. MATCHABLE_FILED_TO_OPERATOR is an associative table that has FKs into MATCHABLE_FILE and OPERATOR. It defines for each field the list of "allowable" operators. For example, integer columns can have =, !=, <, >, >=, <=, but LIKE wouldn't make sense. This table typically holds static list you set up once and extend only when you change your DB model.

Now, all you need is a SP (stored proc) that given a RULE_ID "generates and stores" the SQL text which is the rule. That is a little complex to write, but it is doable (I have done it).

This approach works quite well. And you can easily envision a UI that allows for the generation of rules, what with the name of a rule entered, and a series of dropdowns for selecting the matchable field, then for selecting the appropriate operator and a textbox for matching criteria...followed by an operator of AND, OR before adding in the next matching expression, etc.

This worked quite well for us and provided sophisticated rules to be generated. However, we DID NOT support parenthesis, but that would have been a relatively easy thing to add. I think one way to support that would be to add a precendence "tree". In this approach instead of MATCHING_EXPRESSION having a LIST of tupples for a rule and an ordinal, remove ORDINAL and make it a tree...RULE would have a ROOT_MATCHING_EXPRESSION_ID FK, and then each MATCHING_EXPRESSION record would have nullable LEFT and RIGHT children FKs back to itself.

Good luck.

TR

 

[ESquared]

I guess I didn't explain very well, because I gave you a method to store a list of ANDs and ORs in rows.

[tt]Destinations (DID = DestinationID)
DID Name
1 Bahamas
2 Ontario
3 Tokyo
4 Angkor Vat

PersonHoliday (PID = PersonID)
PID DID Group
1 1 1
1 2 2
1 3 2
1 4 2
2 2 1
2 4 1
2 3 2
2 1 3[/tt]

The data given for PersonHoliday can be thought of as follows:

PersonID 1:
 1         2   <- Group
(1) AND (2,3,4)

1 = (Bahamas) AND
2 = (Ontario OR Tokyo OR Angkor Vat)


PersonID 2:
  1        2       3   <- Group
(2,4) AND (3) AND (1)

1 = (Ontario OR Angkor Vat) AND
2 = (Tokyo) AND
3 = (Bahamas)

This would work great for the simple model that you described. If you need a much more complicated representation then what TJRTech suggested is probably going to be the way to go.