Model many-to-many DB table in class UML

[Tryst]

Hi all,

If I have a database table that is a many-to-many, how would i model this in my UML Class diagram?

I have a table/class called 'Customer' and also another Class/table called 'Service' and then the a many-to-many database table called 'Customer-Service'. Would this be modeled as an Association Class?

Thanks

[«Midnight»]

Model this the same way you would in your database. Create a join table - something like CustomerService - that contains foreign keys appropriate for each of the tables you want to relate. Then create one-to-many relations to the join table from each of the others.

[jeshaw2]

Why a join table?  Does not the UML allow an association with multiplicities of many on both ends?  Or is it that the model is at the PSM level?

[thomaskilian]

The joint table IS (a representation) of the association class. As long as you are on a CIM level it is enough to draw an association with N and M multiplicity on both ends. Then on PIM you can model this (via a transformation) as association class connecting both classes. At last when you create a physical model in the PSM you will transform it into tables like David said.

[«Midnight»]

Actually, I was being careless here. My answer was scoped towards the physical level of data modeling, rather than the conceptual level.

If you were working on something like a CIM you could well use a simple association with appropriate multiplicities. Or, depending on how detailed you want to be - or how your methodology requires - you could use an association class. This might be more in line with the original question, given the phrasing "in my UML Class diagram" from the original question.

I took the liberty of assuming that since the next line used the word "table" - though qualified in each instance - that this was an EA data model.

I answered within the context above. However, I did not document my assumption. Poor form, to say the least. Jim was quite correct to call me on it.  

David

[jeshaw2]

Actually, my comments were directed at Tryst.  It would have been more helpful if the diagram type (in relational db terms) was specified as Logical or physical.  :-/  Imprecise questions beget imprecise responses.  

[Tryst]

Hi and sorry to about the uncertainty in my initial post :-s

Basically, we have the database schema all setup (tables are created in the database). One such database table is the CustomerService table. What I want to know is, with the information I provided in my initial post, how do I represent this (many-to-many) table in a Class diagram?

Thanks

[Paolo F Cantoni]

Hi all,

If I have a database table that is a many-to-many, how would i model this in my UML Class diagram?

I have a table/class called 'Customer' and also another Class/table called 'Service' and then the a many-to-many database table called 'Customer-Service'. Would this be modeled as an Association Class?

Thanks

I'm going to do a "sargasso" here (apologies - bruce).

A table isn't a class and an class isn't a table.  They are both classifiers, but one isn't based on the other.  The fact that Sparx have made Tables based on Classes (via stereotypes) is actually a failing , in my view.

There are conceptual similarities between intersector tables and AssociationClasses, but since in EA AssociationClasses can only be used for binary Associations, there is a limiting factor.

The fun and games Sparx has trying to make Data Modelling work is an indication of the problems I'm alluding to.

If you check for postings by me on the subject of N-ary Associations you'll see some points that might help.

For the present, my advice is model an Associative (Intersector) Table as a Table.  Model tables as tables, create dependencies between classes that may be mapped/transformed to tables via a persistence layer and keep the two concepts separate.

In other words, ignore the fact that Sparx have implemented tables as stereotyped classes.

Paolo

[«Midnight»]

...
A table isn't a class and an class isn't a table.  They are both classifiers, but one isn't based on the other.  The fact that Sparx have made Tables based on Classes (via stereotypes) is actually a failing , in my view.
...
The fun and games Sparx has trying to make Data Modelling work is an indication of the problems I'm alluding to.
...
In other words, ignore the fact that Sparx have implemented tables as stereotyped classes.
...

True Paolo,

But Sparx isn't the original author of our discontent here, but rather another victim.

The profile Sparx uses dates back to the pre-IBM days of Rational. Sparx cites this in their documentation (somewhere). Unfortunately the profile was not widely published, and seems never to have left draft phase (though there was a book that described the profile at the draft level).

Some time ago Sparx suggested they were leaning towards replacing the data modeling function when an upcoming OMG data modeling profile finally saw the light of day. Since that time the OMG work became much broader in scope - perhaps a good thing, but certainly adding complexity to the standardization process - and has apparently gone into (possibly deep) hibernation.

So, 'credit' where such is due.

Perhaps OMG will suddenly revive itself and get on with the new standard sometime soon. We live in hope...

David