How to Decide Between ERD and UML for Data Modeling

database-designerd

A UML diagram can be used both to model database designs and class designs, but an ER diagram should be used exclusively in database problems. How do you pick one graphical representation over the other?

Best Answer

There is no earth-shattering difference between a data model depicted in UML notation versus a data model depicted in any of the many common ER notations. They all depict the most important elements: entity types and relationships.

Depending on which graphical convention you choose, there may be other features of the model which are conveyed, such as the attributes of entity-types, relationship cardinality, strong or weak associations, etc.

The point of the model is to communicate your design.

You should choose the graphical convention that is most expressive of the information that you're trying to convey with the least "noise" (unimportant information).

You should also take into consideration the needs of your audience. No matter how artfully your diagram conveys the information you intend to transmit, if your audience isn't comfortable with the notation then you will have not achieved your goal. If your audience is familiar with IDEF1X then use that, if they are most comfortable with Martin or Chen then use those. If UML does the job and is what is familiar, then UML ER notation is best, otherwise it isn't.

Related Solutions

Modelling Relationships in an E-R Diagram, Table or Foreign Key

Your first option works best when there is the possibility that a lecture can have multiple tutors or when a tutor can teach multiple classes. Your second option works best when you can guarantee that a class will only ever have a single tutor at any one time (otherwise you would need multiple entries in your class table to accommodate extra tutors). The third one will only work if you can guarantee that a tutor will only ever teach one class (the opposite of the second scenario).

If you want/need maximum flexibility then go with option 1. However, you should choose the option that best suits your needs based on the descriptions of the types I have given above.

You can indeed over-normalize a database, and in some cases a de-normalized structure may suit the requirement better - but I don't think this is one of those times.

I hope this helps you.

Sql-server – Implementing foreign keys on generalized data model

You can have two (or more) foreign keys in a table. They can be both regarding the same attribute and referencing different tables. So you could have 2 foreign keys from racoon(pk), one referencing wild(pk) and the other referencing domestic(pk). Something like:

CREATE TABLE raccon
( mammal_id INT  PRIMARY KEY
, FOREIGN KEY (mammal_id)
    REFERENCES domestic (mammal_id)
, FOREIGN KEY (mammal_id)
    REFERENCES wild (mammal_id)
) ;

(assuming all tables have mammal_id as the primary key)

Best Answer

Related Solutions

Modelling Relationships in an E-R Diagram, Table or Foreign Key

Sql-server – Implementing foreign keys on generalized data model

Related Question