CODEWITHSUNDEEP
sqldatabase-designjoin
andrewm
andrewm

Reputation: 2642

Best sql practice for related records

Hi I need be able to link related applications and am trying to work out the best practice table structure for saving, updating and deleting.

I have the following table:

APPLICATION{ApplicationId, Name, Description}

I need to be able to say Application 1 is linked to 2 and 3. Therefore if you open application 2 you'd see that it is linked to application 1 and 3. Then application 3 is linked to 1 and 2.

What is the best table structure for a linked table?

EDIT

My main query is will I need a record for each join ie for applications 1, 2 and 3 would I need 6 records? 1->2, 1->3, 2->1, 2->3, 3->1, 3->2 ?? If not what is the best query to return all linked apps for a given id?

Upvotes: 0

Views: 172

Answers (6)

Randy
Randy

Reputation: 16677

application_association
-------------------------
application_1_id
application_2_id
relationship_type
begin_dt
end_dt

use relationship_type to specify how the applications are related, and use the dates to specify when that relationship was valid

edit:

maybe there is a collective misinterpretation of your use of the word 'linked'.

If you instead mean 'grouped' then you might consider a structure like the following:

group
------------------
group_id
name

application_group
-------------------
application_id
group_id

here you can just place applications into the same 'group' and then query them all back when they are in the same group.

Upvotes: 5

canon
canon

Reputation: 41695

Two more tables.

One for association type:

create table ApplicationAssocType
(
    Id int identity(1,1)
   ,[Description] varchar(128) not null
)

And one for the association itself:

create table ApplicationAssoc
(
    Id int identity(1,1)
   ,ApplicationId1 int not null references Appliation(ApplicationId)
   ,ApplicationId2 int not null references Appliation(ApplicationId)
   ,ApplicationAssocTypeId int not null references ApplicationAssocType(Id)
)

[Edit] To clarify, you'd add a record for each individual link. Add any fields to ApplicationAssoc which pertain to the relationship between the applications specified.

Upvotes: 1

El Ronnoco
El Ronnoco

Reputation: 11912

You need a link table which basically allows you to have a many-to-many relationship between Application and itself.

CREATE TABLE lnkApplication
(ApplicationID1 int, ApplicationID2 int)

GO
ALTER TABLE [dbo].[lnkApplication]  WITH NOCHECK 
ADD  CONSTRAINT [FK_ApplicationLink1] FOREIGN KEY([ApplicationID1])
REFERENCES [dbo].[tblApplication] ([ApplicationID])
GO
ALTER TABLE [dbo].[lnkApplication]  WITH NOCHECK 
ADD  CONSTRAINT [FK_ApplicationLink2] FOREIGN KEY([ApplicationID2])
REFERENCES [dbo].[tblApplication] ([ApplicationID])
GO

Upvotes: 0

Rami Alshareef
Rami Alshareef

Reputation: 7160

LinkedApplication{ID, ApplicationId, LinkedApplicationId}

Upvotes: 0

daniloquio
daniloquio

Reputation: 3902

Application entity has a many to many relationship with itself, so you need another table to store that mapping:

APP_Relationship (ApplicationId, RelatedApplicationId)

Upvotes: 0

JNK
JNK

Reputation: 65187

CREATE TABLE AppLink (App1 int, App2 int)

This is endlessly extendable for as many relations as you need.

Upvotes: 0

Related Questions