Results 1 to 11 of 11

Thread: Design issue

  1. #1
    Join Date
    Aug 2011
    Posts
    8

    Unanswered: Design issue

    Hi guys,

    This forum has been so helpful to me previously. I thought I would ask here before making any changes. I inherited a db and one thing that has bothered me is the implementation of certain tables.

    Basically, there are a dozen entities or so that use an auto-increment as the primary key. Each row of data in them has one or more "responsible" parties. The current design is that every one of those entities has an "association" table for it, ie. Table1 -> Table1_Responsible -> Person, Table2 -> Table2_Responsible -> Person. Each association table has a primary key consisting of a fk from TableX and a fk from the Person table. I, in effect, have 14 tables storing the same data differentiated only by the associated entity.

    It has been my thought that I could just have a "Responsible table" and point all the entities (Table1, Table2, etc.) to it. The identifier for all the rows in each of those tables however is not unique. Table1 has a record with an id of 1. Table 2 has a record with an id of 1. So there is no quick way of distinguishing them and I have struggled with how to consolidate all these tables.

    My current thought is to create the "Responsible" table with a field for the identifier, a field to specify the "entity" (Table1, Table2), and the field for the foreign key to Person. Use a composite primary key consisting of all three fields.

    Would this work? Is it even worth doing? Is there an alternative approach given these conditions?

    Thanks. I will be glad to clarify anything if I have been unclear.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    By using a composite key, you would have to add a new column to each table for the relationship to hold. This seems like overkill. You are combining several tables into one, so you can do something like the following:

    1) create the new combined table, set the identity column as GENERATED BY DEFAULT. This will allow you to enter values into it instead of always creating one.
    2) Do the following for each table pair:
    a) Insert the Responsible table into the new combined table adding an offset to the identity column
    b) drop the FK to the Responsible table
    c) add the offset to the value to the column RESPONSIBLE_ID
    d) create a new FK to the new combined table
    3) Alter the restart value of the identity column of the new combined table to be greater than the largest current value.

    Andy

  3. #3
    Join Date
    Aug 2011
    Posts
    8
    Thanks Andy,

    Could you explain the "offset" to me. I am a little confused by your suggested approach. Probably the way I asked the question and lack of detail.

    I have this.

    Table 1 (Documents)
    Identity| Name | Description

    Table 1 Responsible
    FK Table 1 Identity |FK Owner Identity

    Owner
    Identity | Name

    Then table 2, table 2 responsible, table 3, table 3 responsible, etc.

    You are suggesting a consolidated "Responsible" table like so?

    FK Table X Identity + offset | FK Owner Identity.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What I mean by "offset" is that when you enter the data into the consolidated table, you add the same value to the identity column for each row from the same table.

    For example for Table 1 the offset would be zero, Table 2 the offset would be 10000, Table 3 the offset would be 20000, etc. The increase in offset size between tables depends on the number of rows in each table.

    Here is a visual example:

    Table 1 Before:
    Identity | Name | Description
    ----------------------------
    1 Joe Something
    2 Frank Something else


    Table 2 Before
    Identity | Name | Description
    ----------------------------
    1 Pete Nothing
    2 Sam Nothing else

    Table 3 Before
    Identity | Name | Description
    ----------------------------
    1 Larry Everything
    2 Moe Everything else



    Combined Table
    Identity | Name | Description
    ----------------------------
    1 Joe Something
    2 Frank Something else
    10001 Pete Nothing
    10002 Sam Nothing else
    20001 Larry Everything
    20002 Moe Everything else


    Andy

  5. #5
    Join Date
    Aug 2011
    Posts
    8
    Ok, I think something is getting lost in translation. Let me try it this way.

    Cars
    1 | Ford
    2 | Chevy

    Boats
    1 | Hatteras
    2 | SeaRay

    Drivers
    1 | Bill
    2 | Robert

    The relationship between cars, boats, and drivers is many to many. So right now it is structured like this:

    CarDrivers
    1 | 1
    1 | 2

    Who drives the Ford? Bill and Robert

    BoatDrivers
    1 | 2

    Who drives the Hatteras? Robert

    I only want a VehicleDrivers table though that is used for both Cars and Boats. If cars had one id (numbers) and boats had another id (letters). Pretty simple.

    VehicleDrivers
    1 | 1
    1 | 2
    A | 2

    But, the ids were set up as auto increment and have no distinguish feature so I have this instead.

    VehicleDrivers
    1 | 1
    1 | 2
    1 | 2

    I can't determine if that 1 is for a Ford or for a Hatteras at this point.

    My thought was:

    VehicleDrivers
    1 | Car | 1
    1 | Car | 2
    1 | Boat | 2
    Last edited by Elliott; 02-28-12 at 12:53.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    What is the purpose of this? Are you experiencing any performance issues? Any particular difficulty in maintaining multiple relationship tables? Aesthetic preferences?

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by Elliott View Post
    Ok, I think something is getting lost in translation. Let me try it this way.

    Cars
    1 | Ford
    2 | Chevy

    Boats
    1 | Hatteras
    2 | SeaRay

    Drivers
    1 | Bill
    2 | Robert

    The relationship between cars, boats, and drivers is many to many. So right now it is structured like this:

    CarDrivers
    1 | 1
    1 | 2

    Who drives the Ford? Bill and Robert

    BoatDrivers
    1 | 2

    Who drives the Hatteras? Robert

    I only want a Drivers table though that is used for both Cars and Boats. If cars had one id (numbers) and boats had another id (letters). Pretty simple.

    Drivers
    1 | 1
    1 | 2
    A | 2

    But, the ids were set up as auto increment and have no distinguish feature so I have this instead.

    Drivers
    1 | 1
    1 | 2
    1 | 2

    I can't determine if that 1 is for a Ford or for a Hatteras at this point.

    My thought was:

    Drivers
    1 | Car | 1
    1 | Car | 2
    1 | Boat | 2
    You could do it that way, but with that setup you cannot use RI.

    Andy

  8. #8
    Join Date
    Aug 2011
    Posts
    8
    Quote Originally Posted by n_i View Post
    What is the purpose of this? Are you experiencing any performance issues? Any particular difficulty in maintaining multiple relationship tables? Aesthetic preferences?
    1. Trying to print out an ERD is a mess because of all the extra tables.
    2. It seems rather redundant. It uses 14 tables instead of 1.
    3. Joins are a pain because most queries are presenting information from many of the main tables and I have to join in double the number of tables to get the "drivers" info.

    Are those good reasons? In the end, it just seems a poor design choice that was made.

  9. #9
    Join Date
    Aug 2011
    Posts
    8
    Quote Originally Posted by ARWinner View Post
    You could do it that way, but with that setup you cannot use RI.

    Andy
    I am not sure of another way to do it at this point. The thing that is nice at the moment is if I delete a car, it cascades and the row is removed from the CarDrivers table.

    Now that I explained it better, how do you propose I create that VehicleDrivers table to consolidate the CarDrivers and BoatDrivers tables.

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Elliott View Post
    1. Trying to print out an ERD is a mess because of all the extra tables.
    Aesthetics.
    Quote Originally Posted by Elliott View Post
    2. It seems rather redundant. It uses 14 tables instead of 1.
    Aesthetics.
    Quote Originally Posted by Elliott View Post
    3. Joins are a pain because most queries are presenting information from many of the main tables and I have to join in double the number of tables to get the "drivers" info.
    I doubt that causes you actual physical pain, so, again, aesthetics. I'm sure you realise that, given a single relationship table, you will still have the same number of joins, only you will be joining the same table over and over.
    Quote Originally Posted by Elliott View Post
    Are those good reasons?
    I wouldn't say so. If anything, I would rather ask myself why I have multiple vehicle entities if all that's different between them is type (car vs. boat).

  11. #11
    Join Date
    Aug 2011
    Posts
    8
    You went on to the next issue. I have made that point as well. Right now, I would say that 30 tables could be replaced by 3. It is unlikely though that they are willing to rewrite all the application code.

    Bottom line, you don't think it worth the effort to remove the redundant tables. If so, that works for me.

Posting Permissions

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