It's been a while since my databases course in university, so bear with me as I try to be unambiguous here.

In the domain I'm working in, there are `Sources` and `Destinations`. Both have 2D coordinates (an X and Y coordinate). Both `Source` and `Destination` have separate data in addition to the coordinates. Finally, there is a one-to-many relationship between `Source` and `Destination`.

# Idea #1

Use two tables, one for each type of coordinate:

``````SourceID  |  XCoord  |  YCoord |  Other Source Columns |
--------------------------------------------------------
332       |  33      |  4      |          ...          |
423       |  213     |  342    |                       |
211       |  365     |  12     |                       |

DestinationID  |  Source ID  |  XCoord  |  YCoord |  Other Dest. Columns |
--------------------------------------------------------------------------
455            |  332        |  24      |  4      |          ...         |
892            |  332        |  653     |  32     |                      |
112            |  211        |  5       |  122    |                      |
``````

# Idea #2

Use three tables, where `Coordinates` are stored in their own table, but everything else is the same:

``````CoordinateID  |  XCoord  |  YCoord |
------------------------------------
1             |  33      |  4      |
2             |  213     |  342    |
3             |  365     |  12     |
4             |  24      |  4      |
5             |  653     |  32     |
6             |  5       |  122    |

SourceID  |  CoordinateID |  Other Source Columns |
---------------------------------------------------
332       |  4            |         ...           |
423       |  5            |                       |
211       |  6            |                       |

DestinationID  |  SourceCoordID |  CoordinateID |  Other Dest. Columns |
------------------------------------------------------------------------
455            |  332           |   1           |          ...         |
892            |  332           |   2           |                      |
112            |  332           |   3           |                      |
``````

The differences I can think of are these:

• The second one will perform worse: in order to get the (x, y) coordinates of a source or destination, the source or destination table must be joined with the coordinate table.
• The second one is more maintainable: only one table needs changing if, say, a timestamp needs to be stored with every (x, y) pair.

Are those differences correct?

(I'm happy to remove the next question if it's too open ended or off-topic.) Are there other significant differences?

`JOIN` operations are not as expensive as you might expect. Many times the benefit of normalization will far outweigh the "costs" of joining the tables, specifically if you have "common" coordinates that are used by more than one destination/source.

I would likely model this using two tables, one for coordinates, and one to describe the points (sources and destinations).

For instance:

``````CREATE TABLE dbo.Coords
(
CoordinateID INT NOT NULL
CONSTRAINT PK_Coords
PRIMARY KEY CLUSTERED
IDENTITY(1,1)
, x INT NOT NULL
, y INT NOT NULL
);

CREATE TABLE dbo.Points
(
PointID INT NOT NULL
CONSTRAINT PK_Points
PRIMARY KEY CLUSTERED
IDENTITY(1,1)
, CoordinateID INT NOT NULL
CONSTRAINT FK_Points_CoordinateID
FOREIGN KEY
REFERENCES dbo.Coords(CoordinateID)
, RelativePointID INT NULL
CONSTRAINT FK_Points_RelativePointID
FOREIGN KEY REFERENCES dbo.Points(PointID)
);

INSERT INTO dbo.Coords (x, y)
VALUES (1, 2)
, (1, 3)
, (1, 4);

INSERT INTO dbo.Points (CoordinateID, RelativePointID)
VALUES (1, NULL);

INSERT INTO dbo.Points (CoordinateID, RelativePointID)
VALUES (2, 1);

INSERT INTO dbo.Points (CoordinateID, RelativePointID)
VALUES (3, 2);

SELECT SrcX = src_c.x
, SrcY = src_c.y
, DestX = dest_c.x
, DestY = dest_c.y
FROM dbo.Points src
INNER JOIN dbo.Coords src_c ON src.CoordinateID = src_c.CoordinateID
LEFT JOIN dbo.Points dest ON src.RelativePointID = dest.PointID
INNER JOIN dbo.Coords dest_c ON dest.CoordinateID = dest_c.CoordinateID;
``````

Results:

This design allows any point to serve as the destination for any other source point, effectively allowing you to create a map of directions from one point to any other.

• This looks great, and I appreciate the working code. As you say, your "design allows any point to serve as the destination for any other source point," which is more general than the problem at hand. I definitely can express my problem with your design, but isn't the extra flexibility dangerous? Data that's invalid in the problem becomes valid in the database. ...or am I missing something here? – kdbanman Oct 5 '15 at 15:45
• Yes, as designed above you could have more than one row referencing the same "source" coordinate. You could mitigate that by creating a unique index on the `CoordinateID` column in the `dbo.Points` table: `CREATE UNIQUE INDEX IX_Points ON dbo.Points(CoordinateID);` This would ensure that one, and only one, row from `Coords` can exist in `Points` as a "source". – Max Vernon Oct 5 '15 at 17:27

You seem to be making this needlessly complicated. You have only two entities: Coordinates and Vectors (though you don't specify the name of this one). A coordinate is made up of two points and a vector is made up of two coordinates.

``````Coordinates:
ID  |  XCoord  |  YCoord |
-------------------------|
1  |      33  |       4 |
2  |     213  |     342 |
3  |     365  |      12 |
4  |      24  |       4 |
5  |     653  |      32 |
6  |       5  |     122 |

Vectors:
SourceID  |  DestID |
--------------------|
4  |       1 | -- Source( 24, 4 ), Destination( 33, 4 )
5  |       2 | -- Source( 653, 32 ), Destination( 213, 342 )
6  |       3 | -- Source( 5, 122 ), Destination( 365, 12 )
5  |       4 | -- Source( 653, 32 ), Destination( 24, 4 )
``````

Make DestID the primary key of table Vector to enforce the many-to-one cardinality.

Update: As far as it goes, Idea #2 is a bit more maintainable in that there is a separate table for X-Y Coordinates (Points) and then a Source and Destination table which refers to the appropriate point reference and contains the other data specific to the Source and Destination.

However, you lose some of that maintainability in that it is still needlessly complex. For example, in the Source table you define a one-to-one relationship between Source and the first table -- let's call it Coordinates. But you create a surrogate key for the Source table when you already have a perfectly good surrogate key from the Coordinates table. You could completely drop the SourceID field and rename the CoordinateID to SourceID. You have everything you had before, but with one fewer fields.

Same with the Destination table. You can drop the DestinationID field, rename CoordinateID to DestinationID and change the contents of SourceCoordID to the changed SourceID values.

Your finished design would look like this:

``````CoordinateID  |  XCoord  |  YCoord |
------------------------------------
1             |  33      |  4      |
2             |  213     |  342    |
3             |  365     |  12     |
4             |  24      |  4      |
5             |  653     |  32     |
6             |  5       |  122    |

SourceID  |  Other Source Columns |
-----------------------------------
4         |         ...           |
5         |                       |
6         |                       |

DestinationID  |  SourceCoordID |  Other Dest. Columns |
-------------------------------------------------------
1              |  4             |          ...         |
2              |  4             |                      |
3              |  4             |                      |
``````

Now you have the desired maintainability with a simpler design. The queries will be simpler also.

• There was actually a reason for the distinction between source and destination. Sources and destinations have data other than just coordinates, and source data is different than destination data. That may not have been clear originally, so I've edited to clarify. Also, you're welcome to suggest alternatives, but my actual question is about differences between the choices at hand. – kdbanman Oct 5 '15 at 15:39