Database implementation of a class-diagram

Question

I have the following model:

I created every table in an Oracle database but I don't know how implement the relations between tables. Can you help ?

There are different relations. Which are you concerned about? — qwerty_so, Nov 20 '21 at 13:31
Can you confirm that you have created a different table for each class that appears in the diagram? — Christophe, Nov 20 '21 at 13:56
yes I created a different table for each class that appears in the diagram — DJAMEL, Nov 20 '21 at 16:08

Christophe · Accepted Answer · 2021-11-20T13:53:43.957

There are plenty of possibilities, but here some selected suggestions:

A one-to-many association (or aggregation or composition) is often implemented by having the primary key of the "one" appearing as foreign key in the "many". This technique is sometimes called Foreign key mapping.
Example: the primary key of Address table would be an additional foreign key column it the Fire table.
A many-to-many association is generally implemented with an association table, having two foreign keys that correspond to the primary keys of the associated classes. This is sometimes called association table mapping.
Example: You'd have an additional association table PostFire having two foreign key columns, one corresponding to the primary key of Post and the other to the primary key of Fire.
An association class is implemented with an association table (see above).
Example: To the table implementing Scale, you'd add two foreign key columns, one with the primary key of Client and the other with the primary key of Fire.
Inheritance has many many ways of doing it. If you already created 4 tables, then the trick would be to have the primary key of the "parent" appear as foreign key of the "children", with a unique constraint on this value. Another simpler variant could be to reuse the primary key of the parent as primary key of the children. Both of these techniques are called class table inheritance because each class corresponds to a table.
Example: add a foreign key column to Fireman that corresponds the primary key of User.

P.S: Several points, not directly related to your issue:

There is an UML inconsistency between the association-class name Scale and the association name confirm. An association class is at the same time the association and the class and cave only have one name.
NEVER EVER STORE A PASSWORD IN DATABASE TABLE. Storing a hash code of the password is a much safer approach.

what about the attributes of users ,I create the table of admin , without including theme or not ? — DJAMEL, Nov 20 '21 at 16:00
@DJAMEL in single table inheritance, you do not duplicate the attributes in the tables of the inheriting classes. This means that you need to join several tables to get full objects. An alternative is [concrete table inheritance](https://martinfowler.com/eaaCatalog/concreteTableInheritance.html): no table for abstract classes, but attributes of the parent class are replicated in all its children. It's easier to get full object, but at the expense of redundant table structures and more complicated approach to ensure common behavior. — Christophe, Nov 20 '21 at 16:10

1 Answers1