2

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?

1

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:

enter image description here

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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.