Thread: Multiple foreign keys to the same table
Hi,
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?
Respectfully,
Jorge Maldonado
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.