Which of these database designs is normalized?

Question

I have two possible database designs.

A) a many-to-many relationship between Branches and FiscalYears via a dedicated join table FiscalYearsBranches

B) two (or more) many-to-many relationships between Branches and FiscalYears via other tables (Documents, Factors, etc.)

See the diagrams below. Which of these designs is better and why?

---

Design A: a many-to-many relationship between Branches and FiscalYears via a dedicated join table FiscalYearsBranches

---

---

Design B: two (or more) many-to-many relationships between Branches and FiscalYears via other tables (Documents, Factors, etc.)

---

Answer 1

Design A implies a relationship between FiscalYears and Branches which exists separately to the relationships between these tables and Documents and Factors. This relationship is recorded in FiscalYearsBranches

Design B does not do this, it only defines relationships between FiscalYears and Branches via Documents and Factors.

You should think about the relationship between FiscalYears and Branches: if this exists, as a real life relationship you can talk about outside of the database, even when you're not looking at Documents or Factors, then go with A. If not, go with B, otherwise you are suggesting that this relationship exists when it doesn't.

Answer 2

Based on @dezso and @ypercube comments, I think correct design is:

Table Companies:

    CREATE TABLE [dbo].[Companies] (
    [Id]   INT            NOT NULL,
    [Code] NCHAR (10)     NOT NULL,
    [Name] NVARCHAR (MAX) NOT NULL,
    CONSTRAINT [PK_Companies] PRIMARY KEY CLUSTERED ([Id] ASC)
);

Table FiscalYears:

CREATE TABLE [dbo].[FiscalYears] (
    [Id]         INT            NOT NULL,
    [CompanyId]  INT            NOT NULL,
    [Name]       NVARCHAR (MAX) NOT NULL,
    [StartDate]  DATE           NOT NULL,
    [FinishDate] DATE           NOT NULL,
    CONSTRAINT [PK_FiscalYears] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [IX_FiscalYears] UNIQUE NONCLUSTERED ([Id] ASC, [CompanyId] ASC),
    CONSTRAINT [FK_Companies_FiscalYears] FOREIGN KEY ([CompanyId]) REFERENCES [dbo].[Companies] ([Id])
);

Table Branches:

CREATE TABLE [dbo].[Branches] (
    [Id]        INT            NOT NULL,
    [CompanyId] INT            NOT NULL,
    [Code]      NCHAR (10)     NOT NULL,
    [Name]      NVARCHAR (MAX) NOT NULL,
    CONSTRAINT [PK_Branches] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [IX_Branches] UNIQUE NONCLUSTERED ([Id] ASC, [CompanyId] ASC),
    CONSTRAINT [FK_Companies_Branchs] FOREIGN KEY ([CompanyId]) REFERENCES [dbo].[Companies] ([Id])
);

Table FiscalYearsBranchs:

CREATE TABLE [dbo].[FiscalYearsBranchs] (
    [Id]           INT NOT NULL,
    [CompanyId]    INT NOT NULL,
    [FiscalYearId] INT NOT NULL,
    [BranchId]     INT NOT NULL,
    CONSTRAINT [PK_FiscalYearsBranchs] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_FiscalYearsBranchs_FiscalYears] FOREIGN KEY ([FiscalYearId], [CompanyId]) REFERENCES [dbo].[FiscalYears] ([Id], [CompanyId]),
    CONSTRAINT [FK_FiscalYearsBranchs_Branches] FOREIGN KEY ([BranchId], [CompanyId]) REFERENCES [dbo].[Branches] ([Id], [CompanyId])
);

Table Documents:

CREATE TABLE [dbo].[Documents] (
    [Id]                    INT  NOT NULL,
    [FiscalYearsBranchesId] INT  NOT NULL,
    [No]                    INT  NOT NULL,
    [Date]                  DATE NOT NULL,
    CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Documents_FiscalYearsBranchs] FOREIGN KEY ([FiscalYearsBranchesId]) REFERENCES [dbo].[FiscalYearsBranchs] ([Id])
);

Table Factors:

CREATE TABLE [dbo].[Factors] (
    [Id]                    INT  NOT NULL,
    [FiscalYearsBranchesId] INT  NOT NULL,
    [No]                    INT  NOT NULL,
    [Date]                  DATE NOT NULL,
    CONSTRAINT [PK_Factors] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Factors_FiscalYearsBranchs] FOREIGN KEY ([FiscalYearsBranchesId]) REFERENCES [dbo].[FiscalYearsBranchs] ([Id])
);

---

Answer 3

I think the company determines the fiscal year, not the other way around. This is based on my experience with companies. Your experience may be different.

Hence, I would put an FK to FiscalYearId in the Companies table, and not put any FKs in the fiscal year table.

However, I would put two separate entries in the FiscalYears table for two different companies, even if their fiscal years coincide. This may seem redundant, but it will come in handy when one company changes its fiscal year, and the other company doesn't.

As far as whether Branches, Documents and Factors should include FiscalYearId or not, that question is based on the functional dependencies in your case, ones that I do not know. If Branches, Factors and Documents all use the company's Fiscal calendar, then it's more normalized to leave the FiscalYearId out of those tables. But if each item uses its own FiscalYear, then you have to include the FiscalYearId as an FK in the corresponding table.