How can I relate a primary key field to multiple t

问题:

I have 4 tables:

User 
Reports 
Photos 
Locations 

I am allowed to report photos, users, and locations.

The primary key of my Reports table is (User_Id, Reported_Id)

Reported_Id could belong to any of these 3: photos, users and locations.

How can I represent this relationship in an entity relationship model?

回答1:

The Problem

_{You cannot do this - a foreign key (Reported_Id) cannot reference three tables at once}

Seems to be a problem regarding the understnading of the data ... rather than a technical problem regarding an FK pointing to three PKs.

_{or one of three depending on some other column in your table. Just cannot be done.}

That is not correct. Such a requirement is fairly straight-forward in a Relational database:

• the Relational Model is logical, it is founded on First Order Predicate Calculus (aka First Order Logic).
  Having a solid mathematical basis gives it great power.

• the relations are logical creatures
  Physical Record IDs are not logical

• there is no limit to what can be defined in FOL,
  there is nothing that cannot be defined in FOL
  therefore there is nothing that cannot be defined in a Relational database (and of course SQL, its data sublanguage)
  .

  _{Note that what the "theoreticians" promote and market as "relational" is in fact a 1960's Record Filing System, which has none of the Relational Integrity; Relational Power; or Relational Speed that a database that complies with the Relational Model has. Such systems are identified by their use of physical Record IDs. In such primitive systems, yes, data is not logical, and logical relations or relationships cannot be defined. Further, the SQL code required is horrendous.}

What you are seeking in a logic article, an OR Gate. The specific nature of the OR Gate (there are several forms) needs to be defined: that is the modelling exercise.

The Data

Forget about ID columns, that will only serve to cripple the data modelling exercise. Concentrate on the data, what the data means, and what other data it relates to. Perhaps you are trying to declare something along these lines (these are FOPC/FOL Predicates):

• Each User is Independent
• Each Photo is Independent
• Each Location is Independent
• Each User makes 0-to-n Reports
• Each Report is any of { Photo | Location | User }

That is very loose, we can tighten it up. Let's proceed to ...

Entity Relation • Subtype

This data model (ER level) implements a Non-Exclusive Subtype cluster for Report.

All Predicates are explicit in an IDEF1X data model, and thus can be read from it, however, I have given the relevant Predicates in text form on the right.

Notation

• All my data models are rendered in IDEF1X, the Standard for modelling Relational databases since 1993

• My IDEF1X Introduction is essential reading for beginners

• The IDEF1X Anatomy is a refresher for those who have lapsed.

• Non-Exclusive Subtype - refer to Subtype for full details on Subtype implementation.

  • for contrast or interest re Exclusive Subtype, refer to this answer.

Entity Relation • Optional Column

The above implements the Predicate:

• Each Report is any of { Photo | Location | User }

meaning that a Report must be at least one of { Photo | Location | User }.

However, if that is not the case, if Report can be none of { Photo | Location | User }, that means { Photo | Location | User } are each Optional Columns.

Determination

_{one of three depending on some other column in your table}

Determining which, or all, the Subtypes or Optional Columns that are used for each Report is not an issue:

• Exclusive Subtype
  Yes, that does require a Discriminator column in the Basetype.

• Non-Exclusive Subtype
  There are multiple Subtypes for the Basetype, therefore a Discriminator column in the Basetype is irrelevant.

  • Determination is via a SELECT from the Subtype table (which, by definition, has the exact same PK as the Basetype table).

• Optional Column
  An indicator in the Basetype would be redundant.

  • Determination is via a SELECT from the Optional Column table (ditto).
  • Generally, one would create a VIEW for the Basetype, eg Report_V, and include all possible columns.