CODEWITHSUNDEEP
sqlsql-serverrelationships
Gerry Wagner
Gerry Wagner

Reputation: 97

Is it acceptable to have 2 one to many relationships between 2 tables?

I have a database for utility structures (poles, towers) with wire between them. Each span of wire is connected to two structures. I need to define the relationship between the structures and the spans.

I came up with a relationship between structure A to a "from" foreign key field in the span table and another between structure B and a "To" foreign key field in the span table. Is this acceptable database design?

Here's a diagram: 2 one to many relationships between the structure table and the span table

Upvotes: 2

Views: 1084

Answers (2)

SQLDiver
SQLDiver

Reputation: 2018

Yes, this is quite a common requirement when designing databases and your approach is probably the best way of fulfilling this requirement.

Upvotes: 0

DeadZone
DeadZone

Reputation: 1680

Your model indicates that a span is related to exactly 2 structures. So I think you're good, there. My only comment is that with the names, "fkFromStructureID" and, "fkToStructureID" you imply an orientation that may not exist. It's nit-picky, I admit. But if you do not have a concept of "direction" in your real-world model, you might want to consider different names. If orientation does exist, then this works fine.

Note: If you plan to be able to "walk" the chain of spans and structures using these FK fields, then you'll want to be certain that you manage your orientation on the way into the database. One span that has the From and To backwards will ruin the ability to walk the chain.

Upvotes: 1

Related Questions