How to determine relationship (1:1, 1:n, n:m) between tables when reverse engineering

Question

I have some tables already created in my database and now I need to draw ER diagram for these tables.

1. Identified the primary key and foreign key between the tables
2. Determined pk-fk relationship using the keys
3. Now I need to identify the cardinality between the tables. How do I do this?? Please let me know if there is any set of rules which I need to consider while evaluating 1:1, 1:M and M:M relationships.

Let me take an example of two tables where am struck at:

• Table A has a composite key made of pid and identitytype.
• Table B has a composite key made of pid and maritalid.
• Table A and Table B are associated with each other using pid and pid is not null in both the tables.

Let me know what could be the relationship type between Table A and Table B whether it is 1:1, 1:M or M:M. Also, please let me know the sequence of steps that you followed in arriving at type of relationship conclusion.

Thanks, Dex.

Answer 1

Relationships in the entity-relationship model are very different from what you've got in mind. Relationships are not represented by foreign key constraints - that's the old network data model, and it's limited to binary relationships. The entity-relationship model represents n-ary relationships between entity sets in tables, not between tables.

Foreign key constraints restrict the values of a set of columns to be a subset of the values of another set of columns. They are effectively used to enforce entity sets (domains) - for example, to ensure that every person_id column is a subset of the one that represents all known persons in the system. FK constraints are only used during updates - you could delete all FKs from a database and your SELECT queries and JOINs would work exactly as before, further demonstrating that they don't represent relationships.

A relationship is an association among two or more entity sets, each represented by a suitable key. Relationship instances are always recorded in rows of a table. For example:

• A 1:1 relationship between a driver's license and a person would be represented by having the license key and person key together as two columns of a table, and both (separately) uniquely constrained. Whether this is in a license table, a person table, or a separate driver's license table is an implementation detail.

• A 1:N relationship between cars and their owners would be represented by having the car key and person key together as two columns of a table, and the entity set on the many side uniquely constrained. This is often implemented in the table recording the attributes of the entity on the many side.

The preceding relationships are simple enough that we can denormalize them and don't need to record the relationship in a separate table. For higher relationships, though, we need separate tables:

• An M:N relationship between students and subjects would be represented by having the student key and the subject key together as two columns of a table, and the combination of the two uniquely constrained.

• An M:N:P relationship between suppliers, products and regions would be represented by having the supplier key, product key and region key together as three columns of a table, and the combination of the three uniquely constrained.

• An M:N:1 relationship between regions, products and suppliers (a sole mandate) would be represented by having the region key, product key and supplier key together as three columns of a table, and the combination of region and product keys uniquely constrained.

See the pattern?

Every role/component of a relationship can have a foreign key constraint defined if the characteristic predicate of that entity set (its required attributes) is represented in a different table. That means a single n-ary relationship can require n different FK constraints.

To determine the cardinality of a relationship from an existing table:

1. Determine the entity sets represented in the table. Not all columns represent entity sets - some represent value sets, meaning the values have meaning themselves as labels or measures.

2. Determine which combination of entity sets are uniquely constrained together. These are the many sides of the relationship, and we'll give them variables like M, N, P, etc.

3. Every other entity set is dependent on the previous combination and represented by a 1 in cardinality.

It's not quite that simple. It's possible for a table's key to involve value sets in addition to entity sets. In these cases, we've got a weak entity/identifying relationship/subtyping situation. These are usually (but not always) 1:N relationships in which the child entity's key partially overlaps with the parent entity's key.

For more information, I recommend Peter Chen's paper The Entity-Relationship Model - Toward a Unified View of Data.

Answer 2

You should look at this SO question: postgresql-describe-table

In reality, without looking at the schema definition, without a contra-positive example, you have no way of knowing if the table is a 1:1, 1:n, or n:m relation if n = m = 1.

You can do a scan, but it is the constraints that determine that relationship.

If you don't have that data, then you can only demonstrate 1:n and n:m with examples, but it cannot be proven that 1:1 is not n:m without the constraint definitions.

A 1:1 relationship will look like this:

PK - PK

this can only by one-to-(zero or one), as both tables can only have unique keys, 1:n and n:m are not allowed. This would have to be constrained by software on some level to ensure the PK = PK for the separate tables, or more usually, if you really want 1:1 the data is stored, normalized, in the same table. Otherwise, you need to ensure key coordination by a transactional insert, or whatnot. Auto-generated keys are not advised.

A 1:n relationship will look like this:

PK - FK

the FK (foreign key) defines it to be constrained to a primary key in another table, but can be in multiplicity.

An n:m relationship will look like this:

PK - FK|FK - PK

where there are three tables. Two normalized tables with primary keys (the PKs) and a joining table with FK relationships defining the mapping n:m between the tables.

Of course, all of this could be constrained by code using the database, and hence, the table constraints are the only reliable definition of the data schema.

Foreign Keys must point to Primary Keys in another table, so you can't have FK:FK relationships, and there really is no PK:PK relationship defined by the database. That has to be constrained by transactional insert via code. The usual convention is to store data that is PK:PK in the same table, per a normalized data format.

Okay, so, to add a comment, directed at tables A and B; all you can say for certain is you have primary keys consisting of pid:identitytype and pid:maritalid, and if that is the case, for the sake of discussion, say identitytype and maritalid are ints, then you have int:int and int:int, and it tells you nothing. if identitytype has overlap with maritalid, then there is no way to reliably tell them apart. If you are only going to match on pid, then you have an N:M relationship as you have pid:N-pid:M different possibilities which would lead to an N:M relationship.