How to model database tables to UML classes

[mbd1208]

Hi,

Right now I am busy modeling two classes. The classes are tables comming from my database. In the database design, the multiplicity between the two table is 0..* to 0..* (what we say usally many to many relation). In the database context, due to the many to many multiplicity I need then to create a third table in order to avoid this many to many relation.

So my question is : if I want to model those two tables as class diagram, how to do it? Do I need only two classes (one for each table) with a simple association with their multiplicity  0..* on each part. Or do I need to create three classes?

I am really confused, any help is welcome

Thanks in advance

[Gary W.]

Hi

You can use an Association Class (11th icon down in the Structure toolbox on the left vertical toolbar).

I like this because it also lets me record attributes to this "intersection table".  I rarely find true many-to-many's in relational DB design, especially once the time-varying component gets factored in.  So, I put 'effective/retired date' attributes in the Association Class.



HTH,
Gary

[mbd1208]

Hi,

I was wondering to use an association class, but I was not entirely sure. I think the association class can be a solution to my many-to-many's problem.

But I still have a question: If I use an association class for the intersection table, so the design of my classes will look like the same as the design of my database tables. Is'nt it a problem?

For my side, I found a lot of similarities beween  a database tables designing and a modeling class diagram in UML. Is it true or not?

[Gary W.]

> I think the association class can be a solution to my
> many-to-many's problem.

First off, you need to decide if this is indeed a problem for you.  As part of my work, I use Class Diagrams for the Domain Model (as per Fowler's definition), and perhaps drilling down into detailed design.

The DB design is done in another syntax (either in EA or in Oracle Designer), and the target audience and purpose are quite different.  I don't necessarily have to resolve the many-to-many in my Class Model.

> the design of my classes will look like the same as the
>  design of my database tables. Is'nt it a problem?  

That'll depend on your situation.    If the purpose and target audience is the same, then you may not need two diagrams.  In my case, the Class Model is a deliverable to be signed off, as is the DB Design Model.. but by different groups of people.

> similarities beween  a database tables designing and a > modeling class diagram in UML. Is it true or not?

If you're talking Domain Model versus DB tables, then 'yes' a Domain Model diagram will look like a normalized and well-abstracted DB design.  

However, Class Models can have more detail (e.g. constraints) and functionality (e.g. method) than a DB Design, so it'll look completely different.

Note that you may not even need a 'solution' to your many-to-many problem in the Class Model.  You could simply leave it a many to many between the two classes, and leave the exact details to the implementation.

Gary

[Paolo F Cantoni]

>[size=13][SNIP][/size]

If you're talking Domain Model versus DB tables, then 'yes' a Domain Model diagram will look like a normalized and well-abstracted DB design.  

[size=13][SNIP][/size]

That's because the world is in third normal form!

Paolo

[Gary W.]

Paolo

>  That's because the world is in third normal form!

Assuming this is a 'smart aleck-y' comment, I should point out that one my clients insists on FIFTH normal form!!!  



Gary

[Paolo F Cantoni]

Paolo

>  That's because the world is in third normal form!

Assuming this is a 'smart aleck-y' comment, I should point out that one my clients insists on FIFTH normal form!!!  

 

Gary

Gary,

Never make assumptions...

It wasn't a smart-alecky comment.

The world IS in third normal form.  The rules about how the world works are in fifth normal form...

One of the hardest things as a technical person is when your customer thinks they know more than you...

Hopefully your customer understands the difference... between third and fifth normal form.  If not, then you should enlighten them...

Paolo

[jdeuling]

Hello,

I have a many-to-many relationship within one tabel (is it called a pig's ear ?). I created an intersection table and added the first foreign key constraint.  No problem . If I want to add the second foreign key constraint it doesn't work.  Am  I missing something ?

I have another question regarding Oracle Designer.  Designer won't be supported in the future so my company (wehkamp.nl) has to choose another tool/repository for our data modelling. For what I have seen sofar (not very much) EA would be a good choice. Or are there also other replacements for Oracle's Designer ?

Kind regards,

Jos Deuling.

[mbd1208]

Hi,

Did you check if types of the columns for the parent table and the child one matched? Otherwise try also to reverse the direction of the association link.

Me, personaly I am happy with EA. It is stable and cover almost all aspects for designing a Database or other UML diagrams. Comparatively to other tools I cannot give you more idea, because it the first one I am using.

[Gary W.]

Hi,

>> a many-to-many relationship within one table (is it
>> called a pig's ear ?).

No, the common usage of this term is a reflexive and optional one-to-many relationship.

>>  are there also other replacements for Oracle's Designer ?

None based upon a powerful and extendable metamodel the way Designer was, nor one capable of generating and diff'ing the implemented DB objects.

So, we've gone with EA for the analysis/design portion, but we still use Designer for the Generate DDL portion.  Of course, Designer itself is not fully up-to-date with Oracle 10g functionality (e.g. no geometry columns, spatial indices, use of newer package syntax, etc.).

When I have the time, I'll use EA's PSM/PIM functionality to recreate Designer's Generate DDL capability.   Until then, I'm stuck with post-generation changes to Designer's DDL.

Cheers,
Gary

[jdeuling]

Hello Gary,

So the pig's ear is a  'reflexive and optional one-to-many relationship'. But I still have the problem in EA to model a may-to-many relationship within 1 table. Do you have any clue ?

EA also has a 'generate ddl' functionality. Why use Designer ?

Regards, Jos.

[Gary W.]

Jos,

> But I still have the problem in EA to model a
> many-to-many relationship within 1 table. Do
> you have any clue?

If you want to model this situation, then a single association with '0..*' at both ends will show the logical construct.  

However, it isn't normalized and you cannot implement this in the typical relational database.

If this "many-to-many relationship within 1 table" is truly an accurate description of the structural rules, then I'd show the resultant design by creating a second class with two "0..*" associations to your first class.  This can be implemented in a relational database (when you convert classes to tables).

BTW, what are you modeling?  What is the class name?

> EA also has a 'generate ddl' functionality. Why
> use Designer ?

The client has Designer as the standard (although I'm trying to get EA in there =8^).

EA isn't as knowledgeable about Oracle DB objects (e.g. materialized views, sequences, stored packages, distributed objects,  DB/object privileges), but it's a easier-to-use tool than Designer, and allows UML-notation.

Gary