I am creating a table for my gaming site that will house locations in the game. Some location can be sub-areas (i.e The Regon of Bla in area Na) so in the table I added the colum "Parent Location" but this makes the table reference it's self which seems wrong in it's design to me. Is this the right way to do this or someother way better? Here is my code:
CREATE TABLE dbo.tblLocations
LocationID int IDENTITY(0,1) NOT NULL,
LocationName varchar(250) NOT NULL,
SectionID smallint NOT NULL,
LocationType smallint NOT NULL,
ParentLocation int NULL,
MapFileID int NULL,
CONSTRAINT PK_Locations_LocationID PRIMARY KEY CLUSTERED (LocationID ASC),
CONSTRAINT FK_Locations_SectionID FOREIGN KEY (SectionID) REFERENCES dbo.tblSectionInfo (SectionID),
CONSTRAINT FK_Locations_LocationType FOREIGN KEY (LocationType) REFERENCES dbo.tblLocationTypes (LocationTypeID),
CONSTRAINT FK_Locations_ParentLocation FOREIGN KEY (ParentLocation) REFERENCES dbo.tblLocations (LocationID),
CONSTRAINT FK_Locations_MapFileID FOREIGN KEY (MapFileID) REFERENCES dbo.tblFiles (FileID)
If I get used to envying others...
Those things about my self I pride will slowly fade away.