Re: Multiple foreign keys to the same table

JORGE MALDONADO wrote
> I have a table which has 2 fields (among others): origin and destination.
> Also, I have a single table that represents both origins and destinations.
> If these 2 tables are to be related through foreign keys then both fields
> in the first table will point to the second table. Is this a good practice
> in database design? If not, what is a solution besides making 2 separate
> tables out of the second one?

This is a fairly typically solution but it is technically not fully
normalized - but not in the way you are thinking.

The "problem" is actually with the table having the origin/destination
fields. Namely, what if you later want to add another "waypoint" location -
or many of them?

This is basically the same problem as having "phone1", "phone2", "phone3" on
a table...

Typically you would have an intermediary table that would relate your main
table with the "locations" table using a pair of one-to-many
relationships/FKs and would include a tag field (values: "origin",
"destination") indicating the kind of relationship.

This normalization adds considerable complexity and so you need to decide,
based on your problem domain, whether it is worthwhile.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Multiple-foreign-keys-to-the-same-table-tp5812564p5812567.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.