Add a PostLinkTypes table to SEDE

Question

The PostLinks table in SEDE has a column called LinkTypeId, but there's nothing in the schema that describes what various values of LinkTypeId mean. For I think all of the other "Type" columns, there are corresponding description tables like CloseReasonTypes and PostHistoryTypes and ReviewTaskTypes.

So, it would be nice if we had a new table called PostLinkTypes. I guess it would just have two columns (Id tinyint and Name nvarchar), and probably only two rows, too, seeing as the only values of LinkTypeId I've found are 1 (regular links) and 3 (links to duplicates).

Motivation: I looked at the PostLinks table for the first time in the process of writing this query and had to spelunk through the table to figure out what the LinkTypeId column meant.

Answer 1

The two values you found are the only ones:

PostLinks

• Id primary key
• CreationDate when the link was created
• PostId id of source post
• RelatedPostId id of target/related post
• LinkTypeId type of link
  1. Linked
  3. Duplicate

Until the feature request gets implemented you could setup the table your self (but a simple case when construction is less trouble)

/* setup the missing PostLinkTypes table 
   as a temporary table */
create table #PostLinkTypes(
id  integer,
name nvarchar(200))

/* fill table with lookup values */
insert into #PostLinkTypes values (1,'Linked');
insert into #PostLinkTypes values (3,'Duplicate');

/* end setup */

select top 100
       PostId as [Post Link]
      , RelatedPostId as [Post Link] 
      , plt.Name
      , case 
        when pl.linktypeid = 1 then 'Linked'
        when pl.linktypeid = 3 then 'Duplicate'  
        end as [alternative]
from PostLinks pl
inner join #PostLinkTypes plt on plt.id = pl.linktypeid
where plt.id = pl.linktypeid
order by creationdate desc

You can always check the Database schema documentation for the public data dump and SEDE for explanation of tables, fields and content.