Entity-Relationships for database developers
RISE code generators transforms an ERD into a relational database model. The exact details on how this is accomplished may vary between different code generators, however, the principles remain the same. This article covers the ERD to RDBM mapping principles.
An entity is always mapped to a table. All attributes of the entity are represented as columns of the table. Futhermore, the table will receive an extra ID column. A foreign key referencing the table will reference the ID column.
Relations are implemented either as a foreign key column or by introducing a relation specific table with two foreign key columns. Using a relation specific table is always possible and the most general approach when mapping an ER relation to a foreign key. However, implemting the relation as a foreign key column in an existing table is favoured and used whenever possible since it represents a more efficient solution.
The above example corresponds to the following foreign keys:
Foreign key column Document.Folder references primary key column Folder.ID
Foreign key column Folder.Parent references primary key column Folder.ID
Who implements the foreign key?
In the Document Folder example above the foreign key is implemented at the "many" end of the relation. This is the basic behaviour of RISE. A "one-to-many" relation is implemented by placing a foreign key column in table on the many-side of the relation.
The examples below corresponds to the following foreign keys:
Exit.Highway = Highway.ID
Document.Folder = Folder.ID
Wagon.Train = Train.ID
Document.Type = Type.ID
There's one special case where the relation is implemented as a foreign key column even though the relation isn't "one-to-many". This is the asymmetric "one-to-one" relation shown in the Vehicle Car example below. In this case the foreign key column is placed in the table representing optional side of the relation, i.e. Car below. Read more about specializing relations in the article on Table inheritance.
What about NULL?
When is a foreign key column allowed to be null? A foreign key column is nullable if it represents, i.e. is an implementation of, a relation end-point denoted with the circle (optional) symbol.
The above examples gives the following settings for column nullability:
Document.Type null
Wagon.Train null
Document.Folder not null
Exit.Highway not null
Relational tables
All other relations, such as "many-to-many" and "one-to-one", are implemented using a relation table. This table is named according to the name of the relation and provided with a foreign key column for each referenced table.
The below examples corresponds to the following foreign keys:
PersonAddress.Person = Person.ID and PersonAddress.Address = Address.ID
ManWoman.Man = Man.ID and ManWoman.Woman = Woman.ID
UserRole.User = User.ID and UserRole.Role = Role.ID
DepotCompany.Depot = Depot.ID and DepotCompany.Company = Company.ID
StudentClass.Student = Student.ID and StudentClass.Class = Class.ID
where PersonAddress, ManWoman, UserRole, DepotCompany and StudentClass are all relational tables.
Actually, when RISE introduces a relational table RISE transforms a many-to-many or symmetric one-to-one relation by splitting it into two "one-to-many" relations. You may do this normalization yourself while modeling as shown in the below example. By doing this you will avoid the scenario where RISE introduce relational tables in your final result. It will also give you the option to add attributes to the introduced relation entity.
Related articles:
