I have a scenario where I am using a look up table to add a column of corresponding values to some master table. The issue is that all of the lookups are dependent on two keys/parameters except for one case which has to factor in a third parameter. The general scenario is shown in this table, where the id1 and id2 columns are the parameters used for the look-up and value is the desired value:
Table 1
| id1 | id2 | value |
|---|---|---|
| 1 | 1 | a |
| 1 | 2 | b |
| 2 | 1 | c |
| 2 | 2 | d |
| 3 | 1 | e |
| 3 | 2 | f |
| ... | ||
| 100 | 1 | (depends on id3) |
| 100 | 2 | (depends on id3) |
Basically, when id1=100, the values are dependent on id3 as well:
Table 2
| id1 | id2 | id3 | value |
|---|---|---|---|
| 100 | 1 | 1 | w |
| 100 | 1 | 2 | x |
| 100 | 2 | 1 | y |
| 100 | 2 | 2 | z |
I want to represent the lookup data in such a way that is explicitly defined without the need for expressions (i.e., no need for some behind-the-scenes if-then function that checks if value depends on id3 or not), and that minimizes the need for redundant extra rows where id3 isn't actually applicable.
To solve this issue, I have tried the following approaches of transforming/normalizing/denormalizing the data (based on what I know about those principles), none of which are able to satisfy those criteria:
Idea 1
This involves adding the id3 column to the table, so that every row covers a possible value. The issue with this is that the number of rows in the table is doubled, and the value is duplicated multiple times (i.e. for where id3 is either 1 or 2, even though most of the cases are independent of id3:`
| id1 | id2 | id3 | value |
|---|---|---|---|
| 1 | 1 | 1 | a |
| 1 | 1 | 2 | a |
| 1 | 2 | 1 | b |
| 1 | 2 | 2 | b |
| 2 | 1 | 1 | c |
| 2 | 1 | 2 | c |
| 2 | 2 | 1 | d |
| 2 | 2 | 2 | d |
| 3 | 1 | 1 | e |
| 3 | 1 | 2 | e |
| 3 | 2 | 1 | f |
| 3 | 2 | 2 | f |
| ... | |||
| 100 | 1 | 1 | w |
| 100 | 1 | 2 | x |
| 100 | 2 | 1 | y |
| 100 | 2 | 2 | z |
Idea 2:
One way to avoid this would be to instead add another column (value_2) which would be used when id3 is 2. Even though it keeps the number of rows basically the same, this is a horrible idea for many reasons, so I won't entertain it any further:
| id1 | id2 | value_1 | value_2 |
|---|---|---|---|
| 1 | 1 | a | a |
| 1 | 2 | b | b |
| 2 | 1 | c | c |
| 2 | 2 | d | d |
| 3 | 1 | e | e |
| 3 | 2 | f | f |
| ... | |||
| 100 | 1 | w | x |
| 100 | 2 | y | z |
Idea 3
Instead, another idea would be to leave id3 blank, except where it is a factor, thereby eliminating the need for a bunch of duplicate rows. However, the empty id3 value now has to be handled behind the scenes, which is obfuscated from someone looking at how the lookup is done.
| id1 | id2 | id3 | value |
|---|---|---|---|
| 1 | 1 | a | |
| 1 | 2 | b | |
| 2 | 1 | c | |
| 2 | 2 | d | |
| 3 | 1 | e | |
| 3 | 2 | f | |
| ... | |||
| 100 | 1 | 1 | w |
| 100 | 1 | 2 | x |
| 100 | 2 | 1 | y |
| 100 | 2 | 2 | z |
It seems to me that using "Idea 1" is the only proper solution that explicity defines all scenarios; however, the fact that most of the rows have to be duplicated to accomodate the id3 field, even though they are independent of that value, seems to be bad practice too.
I am wondering if there is a best practice for handling this type of case, presumably something to do either with applying a default value and override where necessary, or some such thing. Any advice would be appreciated.