Was wondering if I can get some helpful pointers....
Heres the problem situation...
I presently have a table say table A, which has a composite primary key...comprising of columns a, b, c, d and e
The additional data included in this table pertains to a certain action type, say Action 1 (based on business requirements). This additional data falls into columns p, q, r, s, t.
(This may be thought of in ER terms as a composite attribute)
Thus the columns a, b, c, d, e, p, q, r, s, t comprises the full table definition. There are no other columns.
Due to new requirements, I am now required to store additional data pertaining to a different action say Action 2. This action 2 may or may not be in addition to action 1, but is definitely far less frequent than action 1.
(In ER terms basically seems like I need to expand the composite attribute mentioned above, to a MULTIVALUED composite attribute)
The "type" of data is identical to that of action 1. In other words I am now required to store additional field information, derived from action 2.....and I could potentially reuse the same columns...But I would need to be able to distinguish if the info. pertains to action 1 or action 2.
How should I go about re-designing this existing table.
Some options are
Create a new table Table 2 to hold info. corresponding to action 2 only, but duplicate the primary key info from table A.
2. Create an identity column in table A, and use the identity column as foreign key in table 2, thereby preventing duplication of the primary key info. Table 2 will contain identity value and action 2 info only.
3. Same as above except, use the indentity column from table A, in table 2 to represent action 1 info. and action 2 info...so there will two rows in here for every indentity, distinguished by an action_type field.
Table A will only contain previous primary key (a, b, c, d, e) info. along with the indentity.
4. Expand table A to hold an action_type field which may be action 1 or action 2, and store two rows directly in this table. (No indentity column and no Table 2)
1. There will be only one row of information for each action..
2. In would need to change about 10 application programs...But the thing is, I need to change them anyway...so as to add capabability for action 2, regardless of a structural change to Table A.
So actually, I think I am at liberty to change Table A in any way I please
From Normalization techniques seems like creating Table 2 to hold info pertaining to Action 1 and Action 2 (two rows), along with a flag indicating which action, is the right way to go...(ie have two rows in here, which map back to the parent table, which would have the key...Foreign Key dependency on the parent table...) This will also facilitate easier expansion, if say Action 3 comes along...
Admittedly, there is NO reason to believe, action 3 will come along, but then again, noone foresaw action2 coming along....
My primary reason for debating this approach, is I am a little reluctant to carry out this "mapping" using an identity column...and thereby introduce a whole new range of problems, including index changes etc...
Also might be worth mentioning, that the info pertaining to action1 and/or action2 may be frequently updated.
if you pull action 1 out of table A and place both actions into table B, leaving only the primary key in table A, then you might as well forget about the identity (surrogate) columns, use the full 5-column natural key in table B, drop table A, rename table B to A, and thus end up with only one table
in other words, just add action 2 to table A with a new "action" column that would become the last column in a 6-column pk
note that if actions 1 and 2 have wildly divergent attribute columns, table A will have to have both sets defined, and then half of them will be null for one type of action, and the other half for the other
this is why people feel it's better to split divergent subtypes into separate tables
in other words, keep action 1 in table A and action 2 in table B and forget about the "action" column, which is now implicit in the table name
benefits of this are that if there are queries that require only one type of action, they can go against just one table
drawbacks are that in order to get both actions in a single query, you need a join
do not use an identity column to relate tables A and B -- just use the same 5-column composite
Presently Table A, has three other columns of info. say z, y, z.
However, info. in these columns has nothing to do wih the data from the actions. Info in the columns x, y, z is specific to the primary key of table A, and will be common to actions 1 and 2. This info. just pertains to certain other attributes.
Basically this info. is to remain functionally unchanged.
>> "Just wondering...but why are you against..identity column usage?
WTF OMG LOL!!
well, i'm not, but that's all i will say now, other than that discussing the use of surrogate versus natural keys will invite heated commentary from database designers, who, when they take a position on the difference, will defend it passionately
i would have table A with a 5-column primary key, the three common columns x,y,z, no action type column, and the action 1 attribute columns
table B will have the same 5-column pk, then its own action 2 attribute columns, and, optionally, a foreign key consisting of the same 5 columns as in the primary key, with this foreign key referencing table A (and its 5-column pk)