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.

I have the following database design:

enter image description here

Script to reproduce:

  /*
    DROP TABLE [dbo].[feedback]
    DROP TABLE [dbo].[action]
    DROP TABLE [dbo].[download]
    DROP TABLE [dbo].[user]
    DROP TABLE [dbo].[company]
    DROP TABLE [dbo].[resource]
    DROP TABLE [dbo].[task]
    DROP TABLE [dbo].[subcategory]
    DROP TABLE [dbo].[category]
  */  

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[user]
    (
      [UserId] [int] IDENTITY(1, 1)
                     NOT NULL ,
      [CompanyID] [int] NOT NULL ,
      [DepartmentID] [int] NOT NULL ,
      [EmailAddress] [varchar](150) NOT NULL ,
      [Title] [varchar](50) NULL ,
      [FirstName] [varchar](100) NOT NULL ,
      [MiddleName] [varchar](100) NULL ,
      [LastName] [varchar](100) NULL ,
      [DateOfBirth] [date] NULL ,
      [ResidentialAddress] [varchar](500) NULL ,
      [ResidentialSuburb] [varchar](150) NULL ,
      [ResidentialPostCode] [varchar](20) NULL ,
      [ResidentialState] [varchar](150) NULL ,
      [ResidentialCountry] [varchar](100) NULL ,
      [PostalAddress] [varchar](500) NULL ,
      [PostalSuburb] [varchar](150) NULL ,
      [PostalPostCode] [varchar](20) NULL ,
      [PostalState] [varchar](150) NULL ,
      [PostalCountry] [varchar](100) NULL ,
      [Phone] [varchar](50) NULL ,
      [Mobile] [varchar](50) NULL ,
      [BrowserDetails] [varchar](500) NULL ,
      [IsActive] [bit] NOT NULL ,
      [LastLogin] [datetime] NULL ,
      [Created] [datetime] NOT NULL ,
      [CreatedBy] [varchar](50) NOT NULL ,
      [LastModified] [datetime] NULL ,
      [LastModifiedBy] [varchar](50) NULL ,
      CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED ( [UserId] 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

CREATE TABLE [dbo].[company]
    (
      [CompanyID] [int] IDENTITY(1, 1)
                        NOT NULL ,
      [CompanyName] [varchar](150) NULL ,
      [DepartmentID] [int] NOT NULL ,
      [DepartmentName] [varchar](150) NULL ,
      [ResidentialAddress] [varchar](500) NULL ,
      [ResidentialSuburb] [varchar](150) NULL ,
      [ResidentialPostCode] [varchar](20) NULL ,
      [ResidentialState] [varchar](150) NULL ,
      [ResidentialCountry] [varchar](100) NULL ,
      [PostalAddress] [varchar](500) NULL ,
      [PostalSuburb] [varchar](150) NULL ,
      [PostalPostCode] [varchar](20) NULL ,
      [PostalState] [varchar](150) NULL ,
      [PostalCountry] [varchar](100) NULL ,
      [Phone] [varchar](50) NULL ,
      [IsActive] [bit] NOT NULL ,
      [Created] [datetime] NOT NULL ,
      [CreatedBy] [varchar](50) NOT NULL ,
      [LastModified] [datetime] NULL ,
      [LastModifiedBy] [varchar](50) NULL ,
      CONSTRAINT [PK_department] PRIMARY KEY CLUSTERED ( [DepartmentID] 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 [company]
ADD CONSTRAINT CompanyID_DepartmentID_UQ
UNIQUE (CompanyID, DepartmentID);
GO

ALTER TABLE [user]
ADD CONSTRAINT Company_User_FK
FOREIGN KEY (CompanyID, DepartmentID) 
REFERENCES company (CompanyID, DepartmentID);
GO

CREATE TABLE [dbo].[resource]
    (
      [ResourceID] [int] IDENTITY(1, 1)
                         NOT NULL ,
      [ResourceName] [varchar](1000) NOT NULL ,
      [ResourceUrl] [varchar](1000) NOT NULL ,
      [IsActive] [bit] NOT NULL ,
      [Created] [datetime] NOT NULL ,
      [CreatedBy] [varchar](50) NOT NULL ,
      [LastModified] [datetime] NULL ,
      [LastModifiedBy] [varchar](50) NULL ,
      CONSTRAINT [PK_resource] PRIMARY KEY CLUSTERED ( [ResourceID] 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

CREATE TABLE [dbo].[download]
    (
      [DownloadID] [int] IDENTITY(1, 1)
                         NOT NULL ,
      [ResourceID] [int] NOT NULL ,
      [UserID] [int] NOT NULL ,
      [DownloadDate] [datetime] NOT NULL ,
      [ActionID] [int] NULL ,
      [Created] [datetime] NOT NULL ,
      [CreatedBy] [varchar](50) NOT NULL ,
      [LastModified] [datetime] NULL ,
      [LastModifiedBy] [varchar](50) NULL ,
      CONSTRAINT [PK_download] PRIMARY KEY CLUSTERED ( [DownloadID] 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].[download]  WITH CHECK ADD  CONSTRAINT [FK_download_resource] FOREIGN KEY([ResourceID])
REFERENCES [dbo].[resource] ([ResourceID])

GO

ALTER TABLE [dbo].[download]  WITH CHECK ADD  CONSTRAINT [FK_download_userid] FOREIGN KEY([UserID])
REFERENCES [dbo].[user] ([UserID])

GO

CREATE TABLE [dbo].[category]
    (
      [CategoryID] [int] IDENTITY(1, 1)
                         NOT NULL ,
      [CategoryTitle] [varchar](200) NOT NULL ,
      [WordpressPostID] [varchar](50) NULL ,
      [IsActive] [bit] NOT NULL ,
      [Created] [datetime] NOT NULL ,
      [CreatedBy] [varchar](50) NOT NULL ,
      [LastModified] [datetime] NULL ,
      [LastModifiedBy] [varchar](50) NULL ,
      CONSTRAINT [PK_category] PRIMARY KEY CLUSTERED ( [CategoryID] 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

CREATE TABLE [dbo].[subcategory]
    (
      [SubCategoryID] [int] IDENTITY(1, 1)
                            NOT NULL ,
      [SubCategoryTitle] [varchar](200) NOT NULL ,
      [CategoryID] [int] NOT NULL ,
      [WordpressPostID] [varchar](50) NULL ,
      [IsActive] [bit] NOT NULL ,
      [Created] [datetime] NOT NULL ,
      [CreatedBy] [varchar](50) NOT NULL ,
      [LastModified] [datetime] NULL ,
      [LastModifiedBy] [varchar](50) NULL ,
      CONSTRAINT [PK_subcategory] PRIMARY KEY CLUSTERED
        ( [SubCategoryID] 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].[subcategory]  WITH CHECK ADD  CONSTRAINT [FK_subcategory_categoryid] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[category] ([CategoryID])

GO

CREATE TABLE [dbo].[task]
    (
      [TaskID] [int] IDENTITY(1, 1)
                     NOT NULL ,
      [TaskTitle] [varchar](200) NOT NULL ,
      [SubCategoryID] [int] NOT NULL ,
      [IsActive] [bit] NOT NULL ,
      [Created] [datetime] NOT NULL ,
      [CreatedBy] [varchar](50) NOT NULL ,
      [LastModified] [datetime] NULL ,
      [LastModifiedBy] [varchar](50) NULL ,
      CONSTRAINT [PK_task] PRIMARY KEY CLUSTERED ( [TaskID] 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].[task]  WITH CHECK ADD  CONSTRAINT [FK_task_SubCategoryID] FOREIGN KEY([SubCategoryID])
REFERENCES [dbo].[subcategory] ([SubCategoryID])

GO

CREATE TABLE [dbo].[action]
    (
      [ActionID] [int] IDENTITY(1, 1)
                       NOT NULL ,
      [ActionTitle] [varchar](250) NULL ,
      [TaskID] [int] NOT NULL ,
      [UserID] [int] NOT NULL ,
      [StartDate] [date] NULL ,
      [EndDate] [date] NULL ,
      [OnGoing] [bit] NULL ,
      [Completed] [bit] NULL ,
      [CompletedDate] [datetime] NULL ,
      [IsActive] [bit] NOT NULL ,
      [Created] [datetime] NOT NULL ,
      [CreatedBy] [varchar](50) NOT NULL ,
      [LastModified] [datetime] NULL ,
      [LastModifiedBy] [varchar](50) NULL ,
      CONSTRAINT [PK_action] PRIMARY KEY CLUSTERED ( [ActionID] 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].[action]  WITH CHECK ADD  CONSTRAINT [FK_action_TaskID] FOREIGN KEY([TaskID])
REFERENCES [dbo].[task] ([TaskID])

GO

ALTER TABLE [dbo].[action]  WITH CHECK ADD  CONSTRAINT [FK_action_UserID] FOREIGN KEY([UserID])
REFERENCES [dbo].[user] ([UserID])

GO

CREATE TABLE [dbo].[feedback]
    (
      [FeedbackID] [int] NOT NULL ,
      [FeedbackDate] [datetime] NOT NULL ,
      [ActionID] [int] NULL ,
      [CategoryID] [int] NOT NULL ,
      [UserID] [int] NOT NULL ,
      [Feedback] [varchar](MAX) NULL ,
      CONSTRAINT [PK_feedback] PRIMARY KEY CLUSTERED ( [FeedbackID] 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

ALTER TABLE [dbo].[feedback]  WITH CHECK ADD  CONSTRAINT [FK_feedback_UserID] FOREIGN KEY([UserID])
REFERENCES [dbo].[user] ([UserID])

GO

ALTER TABLE [dbo].[feedback]  WITH CHECK ADD  CONSTRAINT [FK_feedback_CategoryID] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[category] ([CategoryID])

GO

SET ANSI_PADDING OFF

GO

Database Intent The intent of the database is that a user will complete quizzes (actions) in various sub-categories and have the ability to provide feedback on each action (as well as track the resources the user downloads as they proceed).

Usage Information It's expected that there is an average of 43 actions per subcategory and there will be five subcategories total (to begin with). Our user base is ~30,000 users of which we expect only 5,000 or so to actually use this system.

Is there a better approach to this design? What holes do I have in my design that I should look to resolving?

share|improve this question

closed as too broad by Max Vernon, Phil, Kin, swasheck, RolandoMySQLDBA Sep 4 '13 at 3:10

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Since there are so many possibilities for this, unfortunately I have to vote to close this question. If you have a specific design problem, edit your question to reflect that. –  Max Vernon Sep 3 '13 at 23:48
    
The question is too broad. You need to bake down to a simple problem that can be engaged here on stackexchange. Should be closed. –  samsmith Sep 4 '13 at 0:01
add comment

1 Answer 1

up vote 1 down vote accepted

Yes, you can.

Category and Subcategory should be one table, and you should have a parent_id foreign key pointing to its own primary key. If it's not null, then it's a subcategory.

A User is a User and not a Person. You should also abstract Company and Person into one type (Party), so you are not repeating details. A User belongs to a Party (or has a has-one relationship). Read up on Table Inheritance.

You should also break out Address into its own type. A Party can have zero or more addresses, and multiple Parties can reside at the same Address. So you need a junction table between Party and Address.

A Task (which is really a Question?) can only belong to a Subcategory? You probably want a junction table between Task and Category.

A User can only provide Feedback on a Category? Why don't they provide feedback on the Question?

What is a resource? A file that someone downloads? Calling it File would be clearer.

share|improve this answer
add comment

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