Table relationship question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Table relationship question

Author

Topic

wk1980
Starting Member

4 Posts

Posted - 2011-01-14 : 07:39:11

I'm very new to SQL development and having previously only ever administered SQL Server.

I am creating an ASP.NET site to allow 3rd line engineers to record server problems to aid 1st and 2nd line in finding resolutions.

I have a question regarding three of the tables I have and how they should relate...

My main table is called serverproblem. This table contains fields like error message, resolution steps etc. This links to a second table (devices) which contains a list of all server in the environment.

So when a user hits the asp.net site they can filter by server name using a drop down list populated from serverproblem based on the Name field of the devices table.

I have a third table, servergroup because a problem could relate to more than one server. This table is linked to the serverproblem table allowing a group to be specified by the person inputting the data if the problem applies to a group.

I need a way of relating the servergroup table so that records in the devices table know which group they are in as well as the serverproblem table knowing which servergroup the problem applies to.

Any help would be very gratefully received.

Thanks!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-14 : 08:40:33

Seems to me you want 4 tables. I do see a couple of problems though.

1. Are you assuming that if a server experiences an error, that the error occurred on every server in the group?

2. Are you assuming that the resolution to an error is always the same?

Something close to this appears to be what you're after


Create Table devices(
	deviceId int identity(1, 1) NOT NULL PRIMARY KEY,
	deviceName sysname not null unique
);

Create Table groups (
	groupId int identity(1, 1) NOT NULL PRIMARY KEY,
	groupName varchar(50) NOT NULL unique
);

Create Table serverGroup (
	deviceId int not null,
	groupId int not null,
	Constraint PK_serverGroup PRIMARY KEY (deviceId, groupId),
	Constraint FK_serverGroup_Device FOREIGN KEY (deviceId) REFERENCES devices (deviceId),
	Constraint FK_serverGroup_group FOREIGN KEY (groupId) REFERENCES groups (groupId)
);

Create Table serverproblem (
	problemId int identity(1, 1) not null PRIMARY KEY,
	groupId int not null,
	errorMsg varchar(255) not null unique,
	resolution varchar(max),
	Constraint FK_serverproblem_group FOREIGN KEY (groupId) REFERENCES groups (groupId)
);

wk1980
Starting Member

4 Posts

Posted - 2011-01-14 : 09:34:28

Thanks for the response Russell

1. I want the group to be optional because sometimes an issue will apply to all similar servers but sometimes just to that server.

2. Yes, I am assuming for now that the resolution to an error will always be the same.

These are the table I currently have that are relevant to the this question...

CREATE TABLE [dbo].[Devices](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](255) NOT NULL,

CONSTRAINT [PK_Devices] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[ServerProblem](
[P_Id] [int] IDENTITY(1,1) NOT NULL,
[Svr_Id] [int] NULL,
[Problem] [nvarchar](max) NULL,
[ErrorMessage] [nvarchar](max) NULL,
[Resolution] [nvarchar](max) NULL,
[Mon_id] [int] NULL,
[Grp_Id] [int] NULL,
CONSTRAINT [PK_ServerProblems] PRIMARY KEY CLUSTERED
(
[P_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]

GO
ALTER TABLE [dbo].[ServerProblem] WITH CHECK ADD CONSTRAINT [FK_Server_Group] FOREIGN KEY([Grp_Id])
REFERENCES [dbo].[ServerGroup] ([Grp_Id])
GO
ALTER TABLE [dbo].[ServerProblem] CHECK CONSTRAINT [FK_Server_Group]
GO
ALTER TABLE [dbo].[ServerProblem] WITH CHECK ADD CONSTRAINT [FK_Server_Name] FOREIGN KEY([Svr_Id])
REFERENCES [dbo].[Devices] ([ID])
GO
ALTER TABLE [dbo].[ServerProblem] CHECK CONSTRAINT [FK_Server_Name]

CREATE TABLE [dbo].[ServerGroup](
[Grp_Id] [int] IDENTITY(1,1) NOT NULL,
[ServerGroup] [nvarchar](100) NULL,
CONSTRAINT [PK_ServerGroup] PRIMARY KEY CLUSTERED

So the ServerProblem table pulls a name from the Devices table and a group from the ServerGroup table. I want the ServerGroup value to apply to both ServerProblems and Devices so that I can say something like this in my website logic...

Show records where server name = Name selected from drop down.
And Show problems for groups that Name selected from drop down exists in.

Thanks so much for your help. Going round in complete circles here!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-14 : 09:45:08

In that case, seems that servergroup should have a P_Id column (problem id). This way different servers can be in different groups based on the particular problem...right?

I think it's a bad idea to assume that the resolution will always be the same, but if you're creating a knowledge base, then I suppose multiple solutions could be posted for a specific error message.

To take a simple example, Access Denied error might be a permissions error. It might be a file in use error. It might be caused by an invalid path.

wk1980
Starting Member

4 Posts

Posted - 2011-01-14 : 10:21:56

I may well be wrong here, in fact I probably am but I have a Grp_id field in ServerProblem so that a group can belong to a problem.

I would also like a Grp_id in the Devices table so that I can designate a server as belonging to a group.

I can't seem to create a relationship for a Grp_id located in Devices that relates to ServerProblem. When I do I get the following error message...

Foreign key 'FK_Devices_ServerProblem' references invalid column 'Grp_Id' in referencing table 'Devices'.
Msg 1750, Level 16, State 0, Line 1

Thanks again for your help.

wk1980
Starting Member

4 Posts

Posted - 2011-01-14 : 10:26:36

Sorry got column names mixed up. This is the error...

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.ServerProblem' that match the referencing column list in the foreign key 'FK_Devices_ServerProblem'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-14 : 10:36:45

A foreign Key must reference a Primary Key, or a column defined as UNIQUE.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources