I'm learning PostgreSQL and I'm currently learning types and how to create them. However, I can't understand when it's better to create a table and make a relation with another table than just create a type.

For example, in this class diagram "Team" has got "equiment" of type "Equipment". I don't even know if this diagram is correct, but if so, why is it better to make "Equipment" a type instead of a table?

CodePudding user response：

The model

This diagram is incorrect. It says that a Team is composed of Equipments that will be deleted if the Team gets deleted. Remove the black diamond and it'll be fine.

By the way, the arrow is not wrong, but it is not necessary if your UML class diagram is meant for a relational model: the implementation of an association with an RDBMS will always make it bidirectional.

The database

A user defined data type is an SQL features that allows to create a composite type made of several elements. The goal is that you can use the newly created types like the build-in types.

If you create two user defined types in postgresql for your model, you will therefore have anyway to create two tables each with a column of the given type (more explanations here). So there is no decisive advantage to opt for SQL types for implementing UML classes.

The creation of types is mostly advisable in SQL if you are working with value objects. Value objects have no identity and are solely defined by the values they carry. They are generally as type of several attributes in the same or in different classes/tables.

By the way, in an UML model, you should in principle represent value types with «datatype» classifiers. But some people model such value types with normal classes and it's a topic of discussion.

A typical example of value object in a business application is a CurrencyAmount that would be composed of a monetary value and a currency code.