I have four tables: A, B, C, Z.

Each of the records in A, B, C has a single related record in Z. Thus, tables Am B and C each includes a foreign key to a record in Z.

My question is: How to I include foreign keys in the records in Z back to the related record in A, B and C? Or is there a better way to design this kind of relationship?

my question is why?

what kind of relationship?

you haven't given us anything to go on other than a few letters that you say represent tables that have some kind of undefined relationships

Each record in A belongs to a record in Z.
Each record in B belongs to a record in Z.
Each record in C belongs to a record in Z.

Records in Z belong to either A or B or C.

Does this help?

no, it doesn't

if it were as simple as you say, just create the foreign keys and you're done

obviously it's not that simple, but if you're going to hide the real information behind fake names like A and B and C, i can't help you any further

How about using three foreign keys. In SQL:

ALTER TABLE A ADD CONSTRAINT afk1 FOREIGN KEY (col) REFERENCES z (col);
ALTER TABLE B ADD CONSTRAINT bfk1 FOREIGN KEY (col) REFERENCES z (col);
ALTER TABLE C ADD CONSTRAINT cfk1 FOREIGN KEY (col) REFERENCES z (col);

One way to enforce this is to include a column in table Z that indicates the type of record. This column will also be included in tables A, B, and C, but the values in each of these tables will be constant. Then, you set up a composite foreign key including both the type and the primary key for Z in each of the other tables.
This ensures that each Z record be mapped to only one record in any of the other three tables.

In case it helps, here's an example of what Blindman described:

Data Based : Distributed Keys and Disjoint Subtypes

Arent't you trying to map an object class hierarchy to the database ?
(i.e. in your application are A,B,C objects and inherit from Z ?)

Regards,

Exactly. Nice link. My explanation was rather rushed this morning.

In that case, I use common primary keys.
Suppose Z is the main table and A,B,C are subtypes of Z. AA is subtype of A and S a sequence. When creating a row in table A, I compute a primary key concatening 'A-' to the next value of S. Then when creating the related row in table Z, I set the primary key to the same value.

In table AA, the computed key would be 'AA-nextvalue(S)' and would be stored in primary key columns of table A and table Z.
Of course, table names can be replaced with shorter strings, provided one can associate these prefixes to the tables.

So executing a query on table Z, the prefix of the primary key 'AA-12546' tells me what kind of type it is, (table AA) and the key to retrieve the full thing (which is the OP problem, I think):
Select * from Z,A,AA where Z.ID = A.ID and A.ID = AA.ID and AA.ID = 'AA-12546';

Hope this helps,

Sleepless

Yeah, I think the problem is simple and the answer in Relational Database terms is simple. The issue is obstructed by the fact that your starting point is, you have surrogate keys instead of relational keys in all tables: that is a created problem (it did not exist in the relational model), which you were then seeking a solution for. The best advice is, forget about the spreadsheet model; implement the relational model, and you simply do not have the problem.

Second, the avoidance of standard relational terms is a hindrance to you. Ifis true, thencannot be true.

Relations are identified "one-way" only; the reverse must be expressed differently, it cannot be the same. If the first three statements identifying the nature of each relation (these are called Verb Phrases) are true, then there will be a statement or Verb Phrase for the same three relations, expressed in reverse:
Z is a parent of A
Z is a parent of B
Z is a parent of C
These Verb Phrases actually assist you, the modeller, is resolving the model. What I am saying is, don't avoid the rules, they support each other; they are all there to help you.

Back to the original question:The latter. The parent-child relation already exists, and can easily be determined in either direction without adding anything. (If you need a separate discrete child-parent relation, then it is a diffferent problem: your model is not resolved yet.) We need a concrete example. Here's an example posted in a recent thread, that we can use here; it has the relatinal keys migrated properly (which I believe is central to understanding the problem); it has both straight 1:n parent-child and subtype 1:1 parent-child. (You can post back, confirm, deny)
Z = Answer
A, B, C = AnswerEmployment, AnswerGender, AnswerFirearm
The subtypes here are exclusive, they can be inclusive, no problem.
Assuming of course:
1 We have a relational database with declarative referential Integrity; and foreign keys as per dportas' post. For a child, the existence of the parent is guaranteed, it does not have to be "checked".
2 We are using natural, relational keys, which means compound keys due to migration; not surrogate keys

For a parent Answer, the existence of a child AnswerGender is determined by a simple join:Unless you cripple the database by avoiding relational keys and migration of same, you can join any parent to any grandchild (or great-grandchild, etc) directly (without being forced to access intermediate tables):

Logically incorrect. Think about it, Derek.....

Demonstrably incorrect. I have a sister. My sister has a brother. If only one of us "belongs" to the other, then who has priority?

For the bathroom, she does, for the remote, depends on what's on.

Anyhow, I'm with Rudy on this one. The OP doesn't know his terminology well enough to explain his problem without explaining what the tables are, and he won't post what the actual tables are, so we've got no question.

No. I've supplied my statement complete with logic. Since it is being denied, the attempt at refutation will have to supply its "logic". Until then it is an empty statement devoid of logic and reason.
Logically, by definition, since they are siblings, neither "belongs" to the other; both siblings belong to a common parent. Additionally, that has nothing to do with OP.

Separately, if there are incestuous relations, or if there is a formal relation between the siblings (other than via the common parent), then that should be supported via a separate formal relation

Again nothing to do with OP, the "demonstration" is irrelevant to the issue; the rule against which correctness or lack of it, is unidentified.

Separately, if priority (for the bathroom, or the single bed, or whatever) is required, and not immediately identified by the keys at hand, then a separate method that identifies the priority over whatever object priority is allocated, can easily be implemented.

If clarification is required, I do not have my private definitions of Normalisation or the RM; I am quite happy with Codd and Date; and IDEF1X (required for many years, for DoD and govt work) in addition. A relation exists both-ways with migrated keys (and the other "side" can by ascertained from the single relation, without additional elements, that is the central point to OP); but it is expressed one-way.

If you have questions that are unrelated to the OP, such as these, please open a separate thread.

OK Derek, I will dumb it down for you, but then you have only yourself to blame for looking stupid:

Each record in A, B, and C belongs to one record in Z. Each record in Z belongs to one record in either A, B, or C.
Exactly what you have just claimed is logically impossible.

Now, need I continue to explain how ignorant your other assertions were? Or could you figure that out yourself if you put a little thought into it?