Normalizing sub-categories?

Hi,
I'm designing a database, but am having a problem with categories and sub
categories.
Basically we have documents. Each document has a category, and a sub
category. The selection of sub category is dependant on which category is
selected (so that you cannot select a sub category that does not relate to
its category).
If anyone has any ideas, I would be all ears.
Many thanks in advance.
AndyIf you have a separate table with documents on you can defin a Check
Constraint to check wheter the inserted combination exists in this table. Or
you use Foreign Key Constraint as follows:
CREATE TABLE [dbo].[Tab_Categories] (
[CategoryID] [char] (10),
[SubCategoryID] [char] (10)
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Tab_Docs] (
[DocID] [char] (10) ,
[CategoryID] [char] (10) ,
[SubcategoryID] [char] (10)
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tab_Categories] ADD
CONSTRAINT [PK_Tab_Categories] PRIMARY KEY CLUSTERED
(
[CategoryID],
[SubCategoryID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tab_Docs] ADD
CONSTRAINT [PK_Docs] PRIMARY KEY CLUSTERED
(
[DocID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tab_Docs] ADD
CONSTRAINT [FK_Docs_Tab_Categories] FOREIGN KEY
(
[CategoryID],
[SubcategoryID]
) REFERENCES [dbo].[Tab_Categories] (
[CategoryID],
[SubCategoryID]
)
GO
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Andy" <Andy@.discussions.microsoft.com> schrieb im Newsbeitrag
news:25C8585D-3986-4DC6-9997-160B27BB49CC@.microsoft.com...
> Hi,
> I'm designing a database, but am having a problem with categories and sub
> categories.
> Basically we have documents. Each document has a category, and a sub
> category. The selection of sub category is dependant on which category is
> selected (so that you cannot select a sub category that does not relate to
> its category).
> If anyone has any ideas, I would be all ears.
> Many thanks in advance.
> Andy|||To model this properly you need 3 tables (additional columns omitted for
clarity):
CREATE TABLE Categories (
Category_name VARCHAR(50) NOT NULL,
CONSTRAINT PK_Categories
PRIMARY KEY (Category_name)
)
CREATE TABLE Sub_categories (
Sub_category_name VARCHAR(50) NOT NULL,
Category_name VARCHAR(50) NOT NULL,
CONSTRAINT PK_Sub_categories
PRIMARY KEY (Sub_category_name, Category_name),
CONSTRAINT FK_Sub_categories__Categories
FOREIGN KEY (Category_name)
REFERENCES Categories (Category_name)
)
CREATE TABLE Documents (
Document_Name VARCHAR(100) NOT NULL,
Sub_category_name VARCHAR(50) NOT NULL,
Category_name VARCHAR(50) NOT NULL,
CONSTRAINT PK_Documents
PRIMARY KEY (Documents),
CONSTRAINT FK_Documents__Sub_categories
FOREIGN KEY (Sub_category_name, Category_name)
REFERENCES Sub_categories (Sub_category_name, Category_name)
)
Jacco Schalkwijk
SQL Server MVP
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:25C8585D-3986-4DC6-9997-160B27BB49CC@.microsoft.com...
> Hi,
> I'm designing a database, but am having a problem with categories and sub
> categories.
> Basically we have documents. Each document has a category, and a sub
> category. The selection of sub category is dependant on which category is
> selected (so that you cannot select a sub category that does not relate to
> its category).
> If anyone has any ideas, I would be all ears.
> Many thanks in advance.
> Andy|||Jens,
Thanks for the reply.
Surely having sub-category in the documents table doesn't meet 3rd normal
form, since it relies on a non-key table i.e.category? This is my problem.
Its similar to what I have currently...
Documents
--
Doc ID (PK)
CatID (FK)
SubCatID (FK)
Categories
--
CATID (PK)
Cat Description
SubCategories
--
CatID (PK)
SubCatID (PK)
SubCat Description
...With a constraint between Categories and SubCategories
But I don't like it. Maybe Categories and sub categories is just one of
those things that isn't pretty or meets the rules?
"Jens Sü?meyer" wrote:

> If you have a separate table with documents on you can defin a Check
> Constraint to check wheter the inserted combination exists in this table.
Or
> you use Foreign Key Constraint as follows:
> CREATE TABLE [dbo].[Tab_Categories] (
> [CategoryID] [char] (10),
> [SubCategoryID] [char] (10)
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Tab_Docs] (
> [DocID] [char] (10) ,
> [CategoryID] [char] (10) ,
> [SubcategoryID] [char] (10)
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Tab_Categories] ADD
> CONSTRAINT [PK_Tab_Categories] PRIMARY KEY CLUSTERED
> (
> [CategoryID],
> [SubCategoryID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Tab_Docs] ADD
> CONSTRAINT [PK_Docs] PRIMARY KEY CLUSTERED
> (
> [DocID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Tab_Docs] ADD
> CONSTRAINT [FK_Docs_Tab_Categories] FOREIGN KEY
> (
> [CategoryID],
> [SubcategoryID]
> ) REFERENCES [dbo].[Tab_Categories] (
> [CategoryID],
> [SubCategoryID]
> )
> GO
>
> HTH, Jens Sü?meyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Andy" <Andy@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:25C8585D-3986-4DC6-9997-160B27BB49CC@.microsoft.com...
>
>|||Jacco,
Thanks for that, its basically what I currently have. Should I not worry
about it breaking 3NF? - i.e. In the Documents table, sub_category_name
relies on category_name, and not the primary key?
Many thanks
Andy
"Jacco Schalkwijk" wrote:

> To model this properly you need 3 tables (additional columns omitted for
> clarity):
> CREATE TABLE Categories (
> Category_name VARCHAR(50) NOT NULL,
> CONSTRAINT PK_Categories
> PRIMARY KEY (Category_name)
> )
> CREATE TABLE Sub_categories (
> Sub_category_name VARCHAR(50) NOT NULL,
> Category_name VARCHAR(50) NOT NULL,
> CONSTRAINT PK_Sub_categories
> PRIMARY KEY (Sub_category_name, Category_name),
> CONSTRAINT FK_Sub_categories__Categories
> FOREIGN KEY (Category_name)
> REFERENCES Categories (Category_name)
> )
> CREATE TABLE Documents (
> Document_Name VARCHAR(100) NOT NULL,
> Sub_category_name VARCHAR(50) NOT NULL,
> Category_name VARCHAR(50) NOT NULL,
> CONSTRAINT PK_Documents
> PRIMARY KEY (Documents),
> CONSTRAINT FK_Documents__Sub_categories
> FOREIGN KEY (Sub_category_name, Category_name)
> REFERENCES Sub_categories (Sub_category_name, Category_name)
> )
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Andy" <Andy@.discussions.microsoft.com> wrote in message
> news:25C8585D-3986-4DC6-9997-160B27BB49CC@.microsoft.com...
>
>|||Andy,
Do not store the CategoryID in the Documents table (store only the
SubCategoryID). The Category combobox should be unbound, used only in
the form (not stored in the database).
Razvan|||create table CATEGORY
(
CODE varchar(3), --PK
DESCRIPTION varchar(30)
)
create table SUB_CATEGORY
(
CODE varchar(3), --PK
DESCRIPTION varchar(30)
)
create table CAT_DEF
(
RID uniqueidentifier, --PK
CATEGORY varchar(3), -- FK to CATEGORY
SUB_CATEGORY varchar(3) -- FK to SUB_CATEGORY
)
go
unique constraint on CATEGORY & SUB_CATEGORY
create table DOCUMENT
(
DOC_ID integer, -- PK
DOC_NAME varchar(30),
CAT_DEF uniqueidentifier -- FK to CAT_DEF
)
you don't have to use unqiueidentifiers for the link between DOCUMENT and
CAT_DEF, they could be integers etc. Some may say you don't need the
RID/PRID columns at all, but its just a bit neater than having the category
and sub category fields repeated in DOCUMENT.
CAT_DEF allows you to associate the sub categories to the categories, hence
you cannot incorrectly associate a sub category with a category
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:25C8585D-3986-4DC6-9997-160B27BB49CC@.microsoft.com...
> Hi,
> I'm designing a database, but am having a problem with categories and sub
> categories.
> Basically we have documents. Each document has a category, and a sub
> category. The selection of sub category is dependant on which category is
> selected (so that you cannot select a sub category that does not relate to
> its category).
> If anyone has any ideas, I would be all ears.
> Many thanks in advance.
> Andy|||Category_name depends on sub_category_name, not the other way around. And
even that is only the case if sub_category_name is UNIQUE. If the same
sub_category_name can appear in multiple categories, the model I presented
is in 3NF. If sub_category_names are unique, the model below is in 3NF. It
wasn't clear to me from your original post whether sub_category_names are
unique or not.
CREATE TABLE Categories (
Category_name VARCHAR(50) NOT NULL,
CONSTRAINT PK_Categories
PRIMARY KEY (Category_name)
)
CREATE TABLE Sub_categories (
Sub_category_name VARCHAR(50) NOT NULL,
Category_name VARCHAR(50) NOT NULL,
CONSTRAINT PK_Sub_categories
PRIMARY KEY (Sub_category_name),
CONSTRAINT FK_Sub_categories__Categories
FOREIGN KEY (Category_name)
REFERENCES Categories (Category_name)
)
CREATE TABLE Documents (
Document_Name VARCHAR(100) NOT NULL,
Sub_category_name VARCHAR(50) NOT NULL,
CONSTRAINT PK_Documents
PRIMARY KEY (Documents),
CONSTRAINT FK_Documents__Sub_categories
FOREIGN KEY (Sub_category_name)
REFERENCES Sub_categories (Sub_category_name)
)
Jacco Schalkwijk
SQL Server MVP
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:B1A8D1FE-743E-43BC-B38F-0A784A542C26@.microsoft.com...
> Jacco,
> Thanks for that, its basically what I currently have. Should I not worry
> about it breaking 3NF? - i.e. In the Documents table, sub_category_name
> relies on category_name, and not the primary key?
> Many thanks
> Andy
> "Jacco Schalkwijk" wrote:
>