Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

If I have a table such as this,

CREATE TABLE [dbo].[StreetLight](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](50) NOT NULL,
    [Shape] [geometry] NOT NULL,
 CONSTRAINT [PK_StreetLight] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

What structure is ideal to use to relationally link street lights together. For instance I have street light 1, 2, 3, 4, 5, 6, and I want to say that street light 6 and 2 are related. They might be on the same street for instance.

What are the ways I can do this?

share|improve this question

1 Answer 1

up vote 6 down vote accepted

I would recommend adding another table, StreetLightGrouping. This table should have two columns, StreetLightGroupId and StreetLightId. All the StreetLights under one StreetLightGroupId are part of the same group.

Also, if a StreetLight can belong to a maximum of one group, a unique constraint can be established on the StreetLightId column.

share|improve this answer
1  
Right on the money. It might seem like a basic question, but if you look at my other questions lately it is part of a much bigger problem. Turns out that badly structured data i.e. non relational can confuse SQL server and it creates silly query plans which makes a query that runs instantly turn into one that hasn't finished after 10 minutes. –  peter Sep 6 '12 at 20:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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