Have multiple identical tables or one table with many relationships

Question

I'm having a doubt about what is the best design for my situation. I have a common table (Attachment), and that table will have many 1:N relationships. In the future another tables eventually will relationship with that table. I think I have two options:

Create one separated table to each relationship (1:N);
Create one table with many relationships (0:N);

Example of the first scenario:

CREATE TABLE [Education](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [InstitutionName] [nvarchar](100) NOT NULL,
    [Degree] [nvarchar](100) NOT NULL,
    --other columns
)

CREATE TABLE [EducationAttachment](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FileName] [nvarchar](4000) NOT NULL,
    [Path] [nvarchar](4000) NOT NULL,
    --other columns
    [EducationId] [int] NOT NULL --1:N foreing key
)


CREATE TABLE [Goal] (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Objective] [nvarchar](4000) NOT NULL,
    --other columns
)

CREATE TABLE [GoalAttachment](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FileName] [nvarchar](4000) NOT NULL,
    [Path] [nvarchar](4000) NOT NULL,
    --other columns
    [GoalId] [int] NOT NULL --1:N foreing key
)

--FooTable
--FooAttachment

Example of the second scenario:

CREATE TABLE [Attachment](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FileName] [nvarchar](4000) NOT NULL,
    [Path] [nvarchar](4000) NOT NULL,
    --other columns
    [EducationId] [int] NULL --0:N foreing key
    [GoalId] [int] NULL --0:N foreing key
    [FooId] [int] NULL --0:N foreing key
)

CREATE TABLE [Education](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [InstitutionName] [nvarchar](100) NOT NULL,
    [Degree] [nvarchar](100) NOT NULL,
    --other columns
)

CREATE TABLE [Goal] (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Objective] [nvarchar](4000) NOT NULL,
    --other columns
)

--FooTable

What is the best design option? Or another one?

Hi. This is a faq, you have different types of attachment, google sql/database subtyping/inheritance, and you are using anti-pattern multiple/many FKs to multiple/many tables. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using use one variant search for your title & keywords for your tags. — philipxy, Aug 03 '18 at 20:55

Rohit · Answer 1 · 2018-08-03T14:27:42.650

I prefer single attachments table, especially since all attachments have the same columns(attributes).

The not so nice thing about it is having to add multiple foreign keys to attachment table and making them nullable because attachment can be either goal attachment or education attachment. To get around this you can use join tables and avoid adding columns for all new kind of attachments in the attachment table. for eg:

CREATE TABLE [Attachment](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FileName] [nvarchar](4000) NOT NULL,
    [Path] [nvarchar](4000) NOT NULL
)

CREATE TABLE [Education_Attachment](
    [Education_Id] [int] IDENTITY(1,1) NOT NULL, --foreing key 
    [Attachment_Id] [int] IDENTITY(1,1) NOT NULL, --foreing key
)

CREATE TABLE [Education](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [InstitutionName] [nvarchar](100) NOT NULL,
    [Degree] [nvarchar](100) NOT NULL,
    --other columns
)

Read here. In your 1st approach a new attribute for attachment has to be added to multiple tables. And in second approach you will have nullable foreign key constraint.

But that's back to the first model, except with the added possibility to have shared attachments, and an extra table. So the first approach is pretty clearly better. — David Browne - Microsoft, Aug 03 '18 at 14:32
The first approach would be my last preference, say you decide to save attachment length attribute later. It will lead to alters to all the attachments table. — Rohit, Aug 03 '18 at 14:38

Have multiple identical tables or one table with many relationships

1 Answers1