2

I have a list of header words and words as below and I am trying to understand how best to represent these in a relational table:

Header      Other
Words       Words

abandon     abandoned, abandoning, abandonment, abandons
abstract    abstraction, abstractions, abstractly, abstracts
access      accessed, accesses, accessibility, accessible, accessing, inaccessible  access
accommodate accommodated, accommodates, accommodating, accommodation    accommodate
accompany   accompanied, accompanies, accompaniment, accompanying, unaccompanied    accompany
accumulate  accumulated, accumulating, accumulation, accumulates

The problem is that for every word I have additional information such as pronunciation.

What I am thinking of is two tables:

CREATE TABLE [dbo].[Word] (
    [WordId]         INT           IDENTITY (1, 1) NOT NULL,
    [Word]           NVARCHAR(50)  NOT NULL,
    [Pronounciation] NVARCHAR(100) NULL,
    [Other columns follow ...
)

CREATE TABLE [dbo].[WordRelationship] (
    [WordRelationshipId]  INT IDENTITY (1, 1) NOT NULL,
    [HeaderWordId]        INT NOT NULL,
    [OtherWordId]         INT NOT NULL,
)

I would like to get some suggestions from the members here

2
  • This looks a bit like stemming. Any reason you are rolling your own? Full-text indexing might be able to do what you need if you can tell us more about what your overall goal is.
    – wBob
    May 27, 2016 at 17:43
  • 1
    @wBob: Storing "additional information such as pronunciation" sounds to me like this may be more than just for full-text search support.
    – Andriy M
    May 27, 2016 at 18:04

2 Answers 2

2

As @David-Spillet mentioned, I would recommend against the IDENTITY column in the junction table. I would recommend creating a clustered index with a primary key on both columns, such as:

CREATE TABLE dbo.WordRelationship (
    HeaderWordId        INT NOT NULL,
    OtherWordId         INT NOT NULL,
    CONSTRAINT PK_WordRelationship
    PRIMARY KEY CLUSTERED (HeaderWordId, OtherWordId)
);

The WordRelationshipId column is unnecessary, and will increase the size of the table by 50%.

If you really want to keep the size of the table to an absolute minimum, you may want to ensure only a single variation of each relationship can exist. For example, consider the following:

IF OBJECT_ID('dbo.WordRelationship') IS NOT NULL
DROP TABLE dbo.WordRelationship;
IF OBJECT_ID('dbo.Word') IS NOT NULL
DROP TABLE dbo.Word;
GO

CREATE TABLE dbo.Word(
    WordId INT NOT NULL
        CONSTRAINT PK_Word
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , Word NVARCHAR(50)  NOT NULL
    , Pronounciation NVARCHAR(100) NULL
);

CREATE TABLE dbo.WordRelationship (
    HeaderWordId INT NOT NULL
        CONSTRAINT FK_WordRelationship_HeaderWordId 
        FOREIGN KEY REFERENCES dbo.Word(WordId)
    , OtherWordId INT NOT NULL
        CONSTRAINT FK_WordRelationship_OtherWordId 
        FOREIGN KEY REFERENCES dbo.Word(WordId)
    , CONSTRAINT PK_WordRelationship
        PRIMARY KEY CLUSTERED (HeaderWordId, OtherWordId)
);

INSERT INTO dbo.Word (Word)
VALUES (N'Word')
    , (N'Words');

INSERT INTO dbo.WordRelationship (HeaderWordId, OtherWordId)
VALUES (1,2)
    , (2,1);

SELECT w1.Word
    , w2.Word
FROM dbo.Word w1
    INNER JOIN dbo.WordRelationship wr ON w1.WordId = wr.HeaderWordId
    INNER JOIN dbo.Word w2 ON wr.OtherWordId = w2.WordId

The output:

enter image description here

Pretty clearly, the 2nd row is not really required since the 1st row already captures the relationship. You can prevent that by only inserting rows if the complement does not exist. I've created a stored procedure to do the inserts, which returns the number of rows inserted:

IF OBJECT_ID('dbo.InsertWordRelationship') IS NOT NULL
DROP PROCEDURE dbo.InsertWordRelationship;
GO
CREATE PROCEDURE dbo.InsertWordRelationship
(
    @HeaderWordId INT
    , @OtherWordId INT
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @RetVal INT;
    INSERT INTO dbo.WordRelationship (HeaderWordId, OtherWordId)
    SELECT @HeaderWordId, @OtherWordId
    WHERE NOT EXISTS (
        SELECT 1 
        FROM dbo.WordRelationship wr 
        WHERE (wr.HeaderWordId = @HeaderWordId 
            AND wr.OtherWordId = @OtherWordId)
            OR (wr.OtherWordId = @HeaderWordId 
            AND wr.HeaderWordId = @OtherWordId)
            );
    SET @RetVal = @@ROWCOUNT;
    RETURN @RetVal;
END
GO

The procedure uses a single atomic statement which is unlikely to cause deadlocks.

Now, if you insert (1,2) then try to insert (2,1), it will fail:

This inserts a row:

DECLARE @InsertedCount INT;

SET @InsertedCount = 0;
EXEC @InsertedCount = dbo.InsertWordRelationship 1,2;
IF @InsertedCount = 1 PRINT 'Inserted' ELSE PRINT 'Not Inserted';

But this doesn't:

SET @InsertedCount = 0;
EXEC @InsertedCount = dbo.InsertWordRelationship 2,1;
IF @InsertedCount = 1 PRINT 'Inserted' ELSE PRINT 'Not Inserted';

This shows the rows in the table:

SELECT w1.Word
    , w2.Word
FROM dbo.Word w1
    INNER JOIN dbo.WordRelationship wr ON w1.WordId = wr.HeaderWordId
    INNER JOIN dbo.Word w2 ON wr.OtherWordId = w2.WordId

enter image description here

4

That is pretty much how you have to do it. The relationship table for a many-to-many link like that is often called a junction table (though goes by many other names on occasion: map table, mapping table, bridge table, ...).

If you have more than one class of relationship between words (abbreviation, antonym, synonim, ..) than that should be in the junction table too.

If you don't have multiple relationship types then there may be no need to have WordRelationshipId: the most efficient way to store what you currently have in SQL Server is just HeaderWordId, WordID with the two columns together being the primary key and that key being clustered (though as a matter of style/consistency some people prefer to have a separate surrogate key for all entities, and some frameworks baulk at composite PKs, so YMMV).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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