Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2003
    Posts
    9

    Question Keys of an associative entity

    Hi,
    I have a question regarding the representation of a key attribute in a weak or associative entity in the following scenario:

    table1 (strong entity 1)
    (PK) Limit
    (PK) Channel

    table2 (strong entity 2)
    (PK) TransactionType
    (PK) Channel

    The relationship between table1 and table 2 is M:M so I defined
    table3
    (PK) Limit
    (PK) Channel
    (PK) TransactionType

    Table3 relates the Limit to a TransactionType and since both Limit and TransactionType are specific to channel I think it necessary to include the channel colm in table3 as part of the Primary Key.

    Is this correct, and if so, how would I succinctly describe in proper modelling terms (for a logical model) the fact that "channel" is common for any record in table3?

    The reason I ask is the data-modeller we have here says that by stating the Channel as part of the PK I am really saying:
    Table3:
    (PK) Limit
    (PK) Channel
    (PK) Channel
    (PK) TransactionType

    But physically this colm really needs to be there otherwise we lose the "by channel restriction", so I do not understand what he means when he says I shouldn't "show" it as PK in the logical model, but describe it instead. How and why?

    thx,
    Angelena

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Keys of an associative entity

    I don't understand what your data modeller is saying, either. I could understand if he said that Channel should appear twice in the primary key, with a separate rule to say that they should be equal, but not no times. That doesn't make sense. Suppose you had the following data:

    Table1
    Limit Channel
    ABC 1
    ABC 2

    Table2
    TransactionType Channel
    T1 1
    T1 2

    And suppose in Table3 you wanted to link:
    1) Table1(ABC,1) to Table2(T1,1) and
    2) Table1(ABC,2) to Table2(T1,2)

    If the primary key of Table 3 was just (Limit,TransactionType) then you could record on or the other but not both!

    Or is the data modellers point really that the primary keys of Table1 and Table2 are wrong and should not include the Channel?

  3. #3
    Join Date
    Nov 2003
    Posts
    9
    Hi Andrew,
    sorry for the delayed response - issues.

    You said in your response:
    "I could understand if he said that Channel should appear twice in the primary key, with a separate rule to say that they should be equal"

    I believe this is what our modeller was getting at...
    I think I understand the concept, but how is this depicted in the logical model?
    And am I right in my physical model to represent it as I described earlier?

    thanks,
    Angelena

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    In the logical model you have 2 relationships: table3 to table1 and table3 to table2. The primary keys of both table1 and table2 include the attribute Channel, so this attribute is inherited twice by table3.

    How this is depicted depends on the notation used. For example, using Richard Barker's ERD notation you would not show the inherited attributes at all in table3 - instead you would mark the 2 relationships as being part of the "primary UID" for table3. Table3 would have no attributes at all! There is no way in this notation to indicate that the table1 and table2 records should have the same value for Channel, that would have to be described in notes text about table3.

    In the physical model, you are free to make the design decisions you feel are appropriate. If the 2 Channel values are constrained ALWAYS to be the same, and you don't envisage any exception to that rule ever, then combining them into a single column is OK and commonly done. That column would be the physical implementation of the 2 inherited attributes.

  5. #5
    Join Date
    Nov 2003
    Posts
    9

    Talking Thanks

    Thanks Andrew.
    This is a good answer and really helps me.

    One final question befor I go though, do you (or anyone else out there) know of any sites, or reference texts/articles where I might be able to see some examples of this?
    I spent an hour or so looking for my own answer and examples before I resorted to this (impressive) forum. Theres a lot of theory about modelling out there but none of what I found dealt with relationships between tables with composite keys.

    If not, no problem, you have been more than enough help already.

    thanks again,
    Angelena

  6. #6
    Join Date
    Nov 2003
    Posts
    9

    Thumbs down

    Thumbs down to me : ( - I've just actually noticed your name is Tony, sorry Tony.

    Thanks for your help...

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Don't worry, everyone does that - my fault for using my surname as my user ID.

    I don't have any useful references on data modelling to give you. I'm afraid.

  8. #8
    Join Date
    Oct 2003
    Posts
    87
    Consider this:

    channel(channelID, PK)

    transactionType(channelID, PK,tranactionType, PK)

    limit(channelID, PK, limit)

    Thus, a channel can have many transactionTypes, however, a channel can have but one limit.

    This anything like what you're looking for?
    Oracle - DB2 - MS Access -

  9. #9
    Join Date
    Nov 2003
    Posts
    9
    Hi N-ary,
    please forgive me if I am misunderstanding you, I'm not sure this helps.
    These tables already exist basically as you describe - except that the Limit table has Limit as PK.

    I don't see the description of the relationship I am really interested in, which maps Limits to Transaction Types by channel. One limit has many Trantypes and one trantype has many limits - it is the logical description of the entity replacing this M:M relationship that I am querying.

    Thanks for your input though.

    regards,
    Angelena

  10. #10
    Join Date
    Oct 2003
    Posts
    87
    Yes, your solution works considering its scope.

    However:

    table1 (strong entity 1)
    (PK) Limit
    (PK) Channel

    This implies that a Channel can have many Limits,
    and a Limit can be applied to many Channels

    Is there a maximum Limit for a Channel

    table2 (strong entity 2)
    (PK) TransactionType
    (PK) Channel

    This implies a Channel can handle many TransactionTypes,
    and TransactionTypes can run on many Channels

    Might it be true that TransactionTypes can only apply to certain Channels?

    The relationship between table1 and table 2 is M:M so I defined
    table3
    (PK) Limit
    (PK) Channel
    (PK) TransactionType

    This implies a channel can have many transactionTypes, each having a limit.
    Oracle - DB2 - MS Access -

  11. #11
    Join Date
    Nov 2003
    Posts
    9
    Hi N-ary,
    To answer your questions:
    the maximum limit is configurable, it may chnage over time, but for the purpose of my question it's value is unimportant.

    Yes transaction types may only be applicable to a particular channel so yes table3 is the correct physical implementation.

    Given the restriction on the channel - now that we agree the tables are correctly structured - how would you 'document' this in the logical design?

    Angelena

  12. #12
    Join Date
    Oct 2003
    Posts
    87
    Note that I said "within your scope", but I think your scope is too wide or all-encompassing, it may be you'll need more constraints. Perhaps a question and answer session with the Subject Matter Experts (SMEs) would help narrow your scope. If I understand anywhere near what you're trying to model, the scope is too large and the resulting logical and physical models becomes less valuable.

    For example:
    "the maximum limit is configurable, it may chnage over time, but for the purpose of my question it's value is unimportant.

    Given my limited knowledge of what you are trying to do, it would seen you would need to know, at any point in time, the maximum capacity of a channel. Does a transaction consume a known amount of that capacity, thus, if totalled up, can't exceed the current max.

    "Yes transaction types may only be applicable to a particular channel so yes table3 is the correct physical implementation."

    You would need a domain table of transactions related to channels that restricts transactions that can be used on a particular channel.

    Regards . . .

    Originally posted by Angelena
    Hi N-ary,
    To answer your questions:
    the maximum limit is configurable, it may chnage over time, but for the purpose of my question it's value is unimportant.

    Yes transaction types may only be applicable to a particular channel so yes table3 is the correct physical implementation.

    Given the restriction on the channel - now that we agree the tables are correctly structured - how would you 'document' this in the logical design?

    Angelena
    Oracle - DB2 - MS Access -

Posting Permissions

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