Professional Web Applications Themes

many-to-many relation with descriptive column - MySQL

Hi all, i have a question concerning a many-to-many problem where I want to add an additional value to the mapping table. In my model I have the entities "parameterset", "key" and "mapping". Between parameterset and key I have a many-to-many relationship. Therefore, mapping is used to solve this problem. The entity "mapping" has two colums parameterset_id and key_id. Besides them, I need another column to add a specific value to the mapping (e.g. 1.0, 'Test' or 2006-12-14). The problem here is the datatype of the "value" column which I consider to add to my mapping entity in order to ...

  1. #1

    Default many-to-many relation with descriptive column

    Hi all,

    i have a question concerning a many-to-many problem where I want to add
    an additional value to the mapping table.

    In my model I have the entities "parameterset", "key" and "mapping".
    Between parameterset and key I have a many-to-many relationship.
    Therefore, mapping is used to solve this problem.

    The entity "mapping" has two colums parameterset_id and key_id. Besides
    them, I need another column to add a specific value to the mapping
    (e.g. 1.0, 'Test' or 2006-12-14). The problem here is the datatype of
    the "value" column which I consider to add to my mapping entity in
    order to assign an information to the mapping. I thought about adding 3
    colums for each possible datatype and an additional datatype column in
    the "key" entity to know which column to use for which key.

    Am I completely wrong with this general approach? Any hints and links
    are welcome!
    Thanks in advance!

    Regards,
    André

    andre.bernemann@gmail.com Guest

  2. #2

    Default Re: many-to-many relation with descriptive column


    com wrote:
     

    This sounds like a structural defect in your database, however, if I
    was going to attempt something like this I think I'd create a separate
    table for each datatype, like this:

    FLOATS(id*,float)

    STRINGS(id*,string)

    DATES(id*,date)

    * = Primary key

    The primary keys for each table would be autoincrementing, but the
    increment would be at least as large the largest possible number of
    tables, and the starting value for each would be different - so, for
    example, if there could be a maximum of 10 different datatypes then
    there'd be 10 different tables:

    table 1(starting value: 1 increment 10)
    values 1,11,21,31,etc

    table 2(starting value: 2 increment 10)
    values 2,12,22,32,42,etc

    etc, etc

    You'll need to read up auto_increment_increment and
    auto_increment_offset to understand the mechanics of this.

    This way, you'll be able to UNION your tables for the purposes of the
    query - while maintaining unique IDs across the tables:

    mappings(paramaterset_id*,key_id*,mapping_id)

    * = PRIMARY KEY

    strawberry Guest

  3. #3

    Default Re: many-to-many relation with descriptive column

    com wrote: 

    Andre,

    I agree with Strawberry you may have a defect in your design. Normally
    mapping tables don't require extra columns.

    However, there may be times when it is valid to have an additional
    column, also. So let's back up a sec. What is the meaning (high level)
    of the data in the new column? How does it relate to the specific
    many-to-many link? And what types of data could you have there?

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  4. #4

    Default Re: many-to-many relation with descriptive column


    Jerry Stuckle wrote:
     
    >
    > Andre,
    >
    > I agree with Strawberry you may have a defect in your design. Normally
    > mapping tables don't require extra columns.
    >
    > However, there may be times when it is valid to have an additional
    > column, also. So let's back up a sec. What is the meaning (high level)
    > of the data in the new column? How does it relate to the specific
    > many-to-many link? And what types of data could you have there?
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    Hi,

    thank you for your answer. The idea is to calculate a value of an
    object which is represented by a record in another table. The value
    depends on a set of parameters (variables or keys) but not all objects
    need all possible variables. The number of variables differ
    significantly, hence I decided to add them dynamically instead of a
    column for each of them. A variable can be a date, string or float in
    my scenario (The parameters are requested by a dll to calculate the
    value). Over the time new keys may be necessary for new object types.
    Each object type has a well defined key-configuration.

    André

    andre.bernemann@gmail.com Guest

  5. #5

    Default Re: many-to-many relation with descriptive column

    com wrote: 
    >>
    >>Andre,
    >>
    >>I agree with Strawberry you may have a defect in your design. Normally
    >>mapping tables don't require extra columns.
    >>
    >>However, there may be times when it is valid to have an additional
    >>column, also. So let's back up a sec. What is the meaning (high level)
    >>of the data in the new column? How does it relate to the specific
    >>many-to-many link? And what types of data could you have there?
    >>
    >>--
    >>==================
    >>Remove the "x" from my email address
    >>Jerry Stuckle
    >>JDS Computer Training Corp.
    >>net
    >>==================[/ref]
    >
    >
    > Hi,
    >
    > thank you for your answer. The idea is to calculate a value of an
    > object which is represented by a record in another table. The value
    > depends on a set of parameters (variables or keys) but not all objects
    > need all possible variables. The number of variables differ
    > significantly, hence I decided to add them dynamically instead of a
    > column for each of them. A variable can be a date, string or float in
    > my scenario (The parameters are requested by a dll to calculate the
    > value). Over the time new keys may be necessary for new object types.
    > Each object type has a well defined key-configuration.
    >
    > André
    >[/ref]

    Hmmm, this sounds like a recipe for disaster. For one thing, you're
    keeping information in more than one place. What happens if the
    parameters in the other table change, for instance? You'll need to
    update your link table. And having different types can cause even more
    problems.

    Additionally, how would the dll know if the returned value is a date,
    string or float?

    My suggestion would be to use stored procedures to calculate the
    information dynamically, as required. That way you don't duplicate the
    data, and don't have to worry about keeping the data consistent.

    Alternatively, if you insist on keeping the calculated values in the
    database, I'd probably suggest additional tables. Give the link table
    another column, such as linkId. It's a unique value for that row. Then
    have a table with the linkId and a float, one with the linkId and a
    date, etc. Makes it much easier to add new types. All the dll has to
    do is get the data from the appropriate table.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. One-to-Many Relation Question
    By staufman@gmail.com in forum MySQL
    Replies: 5
    Last Post: November 27th, 07:17 PM
  2. SQL query and possible relation
    By Ryunosuke in forum Coldfusion Database Access
    Replies: 1
    Last Post: August 4th, 03:06 AM
  3. C function taking a relation and returning a similar relation.
    By Ben Martin in forum PostgreSQL / PGSQL
    Replies: 0
    Last Post: January 3rd, 03:23 PM
  4. [PHP-DEV] some public relation for php 5
    By Wolfgang Drews in forum PHP Development
    Replies: 0
    Last Post: November 12th, 10:05 AM
  5. How to autouse Statistics::Descriptive?
    By Bob Showalter in forum PERL Beginners
    Replies: 0
    Last Post: September 26th, 12:28 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139