Class diagram conversion to relational model; Inhe

Question

For a school project, I'm supposed to design the systems of a previous semester's project. We're using UML, creating an extremely simple use case diagram (no <<extend>> type nonsense, just actors pointing at use cases inside of a system), from which we make a class diagram, then a relational model.

Use Case and Class Diagram attempts:

The last diagram is just me eventually thinking that maybe my use cases were the issue.

My application lets people/restaurants list food they're willing to donate. Charities that feed the homeless request food. If there's a match, the charities can create a truck route to pick up food from all their matches.

I'm having trouble figuring out how to represent matching, as well as if this diamond of the FoodItems and matching connecting to route makes any sense. I know inheritance is generally a bad idea in relational modeling. Is it just a matter of having FKs everywhere? I feel like I'm walking in circles.

Answer 1

This object relational mapping topic can be quite complex. But looking at your diagram, here an oversimplified rules of thumbs:

• Map each class with no generalization and no specialization to a relational table. When ID fields are present, these will act as primary key.
• Map each of your classes having two specialization, to one relational table that combines the fields of the class and all the fields of its specialization. This technique is called single inheritance table. It's not always a good solution, but in your cas it could definitively fit.
• For each one to n relationship (1 - 1..*, or 1-0..*) add the primary key (ID) on the 1 side as foreign key in the table on the n side. Do this also for the your aggregates. This is called foreign key mapping.
• For your n to n link (1..* - 1..*) you need to add a relational table, with the ID of the two linked tables (each defined as foreign key, and both together as a combined primary key). This is called association table mapping.
• for your one to one aggregation in the second ItemMatch, I think it cwould be sufficient to add two fields corresponding each to a foodItemID (use different names).
• You also need to define as optional (nullable) the foreign keys that you've added as result of a 0..* relation

Additional readings:

• Martin Fowler's excellent book "Patterns of Enterprise Application Architecture" explains the pros and cons of several mapping strategies for inheritance in full details if want to know more. In addition to the simple single inheritance table, you'll find also more complex alternatives approaches like class table inheritance, concrete table inheritance and inheritance mappers.
• Web article Mapping object to data model suggested by granier in the comments (thanks !)