0

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


enter image description here


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


enter image description here

9
  • 1
    Both are equally ugly.
    – beeks
    May 20, 2015 at 9:11
  • 1
    Please get rid of the sample tables, they're really confusing the picture. May 20, 2015 at 9:18
  • 1
    Is a fiscal year really bound to a company? (I am no financial expert, just asking.) If yes, what will prevent you from coupling a fiscal year of company X with a branch of company Y?
    – dezso
    May 20, 2015 at 9:35
  • 1
    And the word is "Branches", not "Branchs". May 20, 2015 at 9:49
  • What @dezso noticed is right. You probably need to include the CompanyID in the FiscalYearsBranches table - and modify the FKs accordingly - to avoid that issue. Check this question: dba.stackexchange.com/questions/49513/constrains-relationship/… May 20, 2015 at 9:52

3 Answers 3

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.

0

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])
);

enter image description here

2
  • This one still has the same problem I asked about.
    – dezso
    May 21, 2015 at 10:15
  • @dezso. I've tested and I think the problem is solved. It is possible to explain more?
    – user66830
    May 22, 2015 at 16:23
0

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.

Your Answer

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