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 wrote:

> 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é

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

andre.bernemann@gmail.com wrote:
> 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,

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.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:

> andre.bernemann@gmail.com wrote:
> > 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,
>
> 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.
> jstucklex@attglobal.net
> ==================

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 wrote:
> Jerry Stuckle wrote:
>
>
>>andre.bernemann@gmail.com wrote:
>>
>>>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,
>>
>>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.
>>jstucklex@attglobal.net
>>==================
>
>
> 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é
>

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.
jstucklex@attglobal.net
==================