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

  • 1
    Both are equally ugly. – beeks May 20 '15 at 9:11
  • 1
    Please get rid of the sample tables, they're really confusing the picture. – Colin 't Hart May 20 '15 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 '15 at 9:35
  • 1
    And the word is "Branches", not "Branchs". – ypercubeᵀᴹ May 20 '15 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/… – ypercubeᵀᴹ May 20 '15 at 9:52
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.

| improve this answer | |
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

| improve this answer | |
  • This one still has the same problem I asked about. – dezso May 21 '15 at 10:15
  • @dezso. I've tested and I think the problem is solved. It is possible to explain more? – H.Radmehr May 22 '15 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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.