A DB Design 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

SQL Server Administration (2005)

A DB Design question

Author

Topic

Peace2007
Posting Yak Master

239 Posts

Posted - 2009-01-21 : 03:50:35

I'm having Users, Tasks, and Categories:
each user has many tasks
each task may be in any or one category
each user has many categories
I want it when a user is deleted, his tasks and categories be deleted as well.
and When a category is deleted the tasks that were in that category know that their in no category
and when a task is deleted its category should not be deleted

could you give me the design of tables and it primary and foreign keys as well as cascade properties?
Thanks in advance

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-01-21 : 04:50:45

CREATE TABLE [dbo].[users](
[user_id] [uniqueidentifier] NOT NULL,
[name] [varchar](50) NULL,
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[user_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Tasks](
[task_id] [uniqueidentifier] NOT NULL,
[description] [varchar](100) NULL,
CONSTRAINT [PK_Tasks] PRIMARY KEY CLUSTERED
(
[task_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[category](
[category_id] [uniqueidentifier] NOT NULL,
[description] [varchar](100) NULL,
CONSTRAINT [PK_category] PRIMARY KEY CLUSTERED
(
[category_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[UserTasks](
[user_tasks_id] [uniqueidentifier] NOT NULL,
[user_id] [uniqueidentifier] NULL,
[task_id] [uniqueidentifier] NULL,
CONSTRAINT [PK_UserTasks] PRIMARY KEY CLUSTERED
(
[user_tasks_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[UserTasks] WITH CHECK ADD CONSTRAINT [FK_UserTasks_Tasks] FOREIGN KEY([task_id])
REFERENCES [dbo].[Tasks] ([task_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[UserTasks] WITH CHECK ADD CONSTRAINT [FK_UserTasks_users] FOREIGN KEY([user_id])
REFERENCES [dbo].[users] ([user_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
CREATE TABLE [dbo].[TaskCategories](
[taskCat_id] [uniqueidentifier] NOT NULL,
[task_id] [uniqueidentifier] NULL,
[category_id] [uniqueidentifier] NULL,
CONSTRAINT [PK_TaskCategories] PRIMARY KEY CLUSTERED
(
[taskCat_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[TaskCategories] WITH CHECK ADD CONSTRAINT [FK_TaskCategories_category] FOREIGN KEY([category_id])
REFERENCES [dbo].[category] ([category_id])
GO
ALTER TABLE [dbo].[TaskCategories] WITH CHECK ADD CONSTRAINT [FK_TaskCategories_Tasks] FOREIGN KEY([task_id])
REFERENCES [dbo].[Tasks] ([task_id])

Peace2007
Posting Yak Master

239 Posts

Posted - 2009-01-21 : 05:15:11

Thank you for the complete script
I may have forgotten to say one point.
I need to let it delete tasks but this design doesn't let a task to be deleted when it relates to a category

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-01-21 : 05:41:41

Why u don't like to use triggers for this CASCADE delete/update

Peace2007
Posting Yak Master

239 Posts

Posted - 2009-01-21 : 05:53:44

I'd like to be sure that there's no other design solution

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources