1

I have a table in my SQL Server that's called "Regions", it has information about 9 regions and the 10th row holds the info about our Division. Now that we will be merging another 2 divisions with its regions, Should I add them all in the same table like I have currently or start separating them by having a "Division" table and "Regions" table?

Which one is the best approach to pull reports from, like pulling documents implementing in specific regions from a specific division? how can I reference that, please advice. Thanks in advance.

1 Answer 1

2

from your question i understand that you have a "One to many" relationship between The Division to the Regions,

the best way with this kind of relational data from my experience is to create a Division table and a Region table that holds the DivisionID.

that will also use less storage space because you won't need to write the division name in string, you can reference the int ID column value that uses less space than the string(char/varchar etc.) :) example

    CREATE TABLE  dbo.Division
(
    [ID] [int] NOT NULL IDENTITY(1,1) CONSTRAINT PK_Division PRIMARY KEY,
    [Name]  NVARCHAR(255) NOT NULL
)

CREATE TABLE  dbo.Region 
(
    [ID] [int] NOT NULL IDENTITY(1,1) CONSTRAINT PK_Region PRIMARY KEY,
    [Name]  NVARCHAR(255) NOT NULL,
    DivisionID INT CONSTRAINT FK_DivisionRegion FOREIGN KEY REFERENCES dbo.Division (ID)
)

shimon.

2
  • Thank you so much, I will add the additional table then and referrence the foreign key in the other. The FK have to accept nulls right? as when I add the regions for the specific division, the DivisionID for the other regions will be Null in the Region Table..
    – Mary
    Jun 24, 2014 at 16:10
  • you can make it accept nulls, or insert the division before the region. its more a question of how you use it and write to the db.. Jun 25, 2014 at 5:30

Your Answer

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

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