The purpose of this set of pages is to come up with a set of rules for translating from ER diagrams to table definitions to SQL create statements. The first set of links below analyze a series of six ER diagrams showing relationships between two entities (that is, the binary relations). There are actually ten types of relationships between two entities when considering connectivity and optionality (list them if you don't believe me); however, the relationships that are not listed are subsumed by one of the relationships that are listed. Each of the following relationships requires a different set of tables to be created. Thus, the following listing contains information on how to translate all ten types of relationships from ER diagram to SQL.
For each relationship I explore alternative table structures before settling on a set of recommended table structures. Look at the given tables as a recommendation for the tables you should create; these are not absolute but default rules to follow. If you have a reason for deviating from these recommendations, then do it. However, you should have a good reason because these are fairly established recommendations that work in many circumstances.
After defining the table structures, I discuss how these tables should be represented in an SQL create statement. Below the discussion of binary relationships, other relationships are analyzed in the same fashion.
If needed, I have some information about how to read the table structure representation and how to read the SQL create statements. Further, if you're interested, I have put together a list of rules for database design and implementation that I follow.
|Binary||Recursive (see note below)||Other structures|
Since the translation from recursive relations to SQL is so similar to the translation of regular binary relations, the discussion in this recursive relation section is much more limited than in the binary relation section. Also, I use one basic recursive relation for an example, and simply change the cardinality, existence, and optionality as appropriate. The example is of an employee who manages other employees.