Results 1 to 7 of 7
  1. #1
    Join Date
    May 2002
    Posts
    39

    Unanswered: Guidance for table design

    Hi...
    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
    1.
    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)

    What do you think??

    Thanks in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    some information that is missing that would certainly help:

    - how many rows of each action

    - how many application programs would need to be rewritten if there's a change in tableA structure

    all things considered, i'd probably go for option 1


    rudy
    http://r937.com/

  3. #3
    Join Date
    May 2002
    Posts
    39
    Thanks for your reply..

    Here's the additional info...

    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.

    Let me know if you have any other questions...

    Your thoughts greatly appreciated.

    Thanks again !

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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


    rudy

  5. #5
    Join Date
    May 2002
    Posts
    39
    OOOPS ! I screwed UP !

    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.

    Sorry about the confusion...

    Thanks!

  6. #6
    Join Date
    May 2002
    Posts
    39
    Makes sense...

    Just wondering...but why are you against..identity column usage?

    Also can you review the presence of the additional columns x, y,z?

    Thanks a mill!

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    >> "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)

    it's a one-to-zero-or-one relationship

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •