How to classify items in a database under two or more classifications
-
Hi I am trying to build a structure that allows items to be classified under 2 or more categories. There are certain items that have to be related to at least 2 categories so that when either category is displayed it would show that company. This is so an end user could search under one category, mobile phones for example, and display results including Samsung but also search for Televisions or air conditioners and also have Samsung displayed. How could you build a structure that would do this? Thanks for any comments
-
You can define the relationship using what's called a resolve table. It resolves the many-to-many. Here's an example using users and categories. I only put a few columns in each table, but this is just for illustrative purposes.
if OBJECT_ID('dbo.Users', 'u') is not null drop table dbo.Users;create table dbo.Users (
ID integer not null identity(1, 1),
constraint Users_PK primary key (ID),
Username Varchar(32) not null,
EntryDate datetime not null default getdate());
if OBJECT_ID('dbo.Categories', 'u') is not null drop table dbo.Categories;
create table dbo.Categories (
ID integer not null identity(1, 1),
constraint Categories_PK primary key (ID),
Description Varchar(40) not null,
EntryDate datetime not null default getdate());
if OBJECT_ID('dbo.UserCategories', 'u') is not null drop table dbo.UserCategories;
create table dbo.UserCategories (
ID integer not null identity(1, 1),
constraint UserCategories_PK primary key (ID),
UserID integer not null
constraint UserCategories_Users_FK
foreign key (UserID)
references dbo.Users(ID),
CategoryID integer not null
constraint UserCategories_Categories_FK
foreign key (CategoryID)
references dbo.Categories(ID),
EntryDate datetime not null default getdate());
The UserCategories table contains a foreign key to Users and one to Categories. One user can have many categories and one category can contain many users. You can query the user categories like this:
select u.Username, c.Description
from dbo.Users u
inner join dbo.UserCategories uc on uc.UserID = u.ID
inner join dbo.Categories c on uc.CategoryID = c.ID
order by u.Username, c.Description;
-
In addition to what Ed says, you may need to enforce uniqueness on the user-category pair; also (for performance) you may want to have a index on whichever of the pair isn't first in the uniqueness constraint. That can be done like this:
create unique index UserCategory_unique_index on UserCategories(CategoryID, UserID) ;create index UserCategories_User_index on UserCategories(UserID) ;
Alternatively the unique index could have the key in the opposite order (UserId first) and the other index replaced by an index on CategoryID.
Another possibility is to get rid of the ID column in the third table and make the unique index the primary key, but that risks table fragmentation if the table has frequent new entries and you might have to decide carefully in which order the columns of the unique key should be to minimise the need for index rebuilds.
Tom
-
Thanks guys. So every row in the User and Categories table will be linked to the UserCategories table? I know I am missing something obvious as they both tables have data but the query shows no results
-
assuming you are using Ed's solution....suggest that you script out the data you have inserted into the exmaple tables he gave you....(eg INSERT )
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
I didn't create primary keys with my inserts into the example tables- assume this is the problem?
-
tomsharp85 (7/11/2015)
I didn't create primary keys with my inserts into the example tables- assume this is the problem?It shouldn't be if you used Ed's primary key definitions.
Did you remember to insert data into the third table as well as into the first two? People sometimes forget to do that and end up trying to kick themselves when they realise they have.
Tom
-
:hehe: No, I didn't - but now I am more confused as what entries should I be inserting into these columns
-
Here's an example. I changed the code provided by Ed, not because of it being wrong, but because I don't like columns named simply ID and don't like identities on tables than only work as a many-to-many relationship.
Go through the code and read the comments.
if OBJECT_ID('dbo.UserCategories', 'u') is not null drop table dbo.UserCategories;
if OBJECT_ID('dbo.Users', 'u') is not null drop table dbo.Users;
if OBJECT_ID('dbo.Categories', 'u') is not null drop table dbo.Categories;
CREATE TABLE dbo.Users (
UserID integer NOT NULL IDENTITY(1, 1),
CONSTRAINT Users_PK PRIMARY KEY (UserID),
Username Varchar(32) NOT NULL,
EntryDate datetime NOT NULL DEFAULT GETDATE());
CREATE TABLE dbo.Categories (
CategoryID integer NOT NULL IDENTITY(1, 1),
CONSTRAINT Categories_PK PRIMARY KEY (CategoryID),
Description Varchar(40) NOT NULL,
EntryDate datetime NOT NULL DEFAULT GETDATE());
CREATE TABLE dbo.UserCategories (
UserID integer NOT NULL
CONSTRAINT UserCategories_Users_FK FOREIGN KEY (UserID)
REFERENCES dbo.Users(UserID),
CategoryID INTEGER NOT NULL
CONSTRAINT UserCategories_Categories_FK FOREIGN KEY (CategoryID)
REFERENCES dbo.Categories(CategoryID),
CONSTRAINT UserCategories_PK PRIMARY KEY (UserID,CategoryID),
EntryDate datetime NOT NULL DEFAULT GETDATE());
--Assuming the tables are completely new for this example
INSERT INTO dbo.Users( UserName) VALUES( 'Samsung'); --This would get ID 1
INSERT INTO dbo.Users( UserName) VALUES( 'Apple'); --This would get ID 2
INSERT INTO dbo.Categories( Description) VALUES( 'Mobile phones'); --This would get ID 1
INSERT INTO dbo.Categories( Description) VALUES( 'Televisions'); --This would get ID 2
INSERT INTO dbo.Categories( Description) VALUES ( 'Air Conditioners'); --This would get ID 3
INSERT INTO dbo.UserCategories( UserID, CategoryID)
VALUES (1,1), -- Samsung - Mobile phones
(1,2), -- Samsung - Televisionss
(1,3), -- Samsung - Air Conditioners
(2,1); -- Apple - Mobile phones
-- Here we can check how the data looks on our tables
SELECT * FROM dbo.Users;
SELECT * FROM dbo.Categories;
SELECT * FROM dbo.UserCategories;
-- Here we have an example on how to join them to get all the information.
SELECT u.UserID,
u.Username,
c.CategoryID,
c.Description
FROM dbo.Users u
JOIN dbo.UserCategories uc ON u.UserID = uc.UserID
JOIN dbo.Categories c ON uc.CategoryID = c.CategoryID;
-
I don't like having to look up identity numbers to insert into tables, so rather than inserting literals into the UserCategories table I would use something like
INSERT INTO dbo.UserCategoriesSELECT UserID, CategoryID, getdate()
FROM dbo.Users cross join dbo.Categories
WHERE UserName = 'Samsung'
AND Description IN ('Mobile phones', 'Televisions', 'Air Conditioners') ;
INSERT INTO dbo.UserCategories
SELECT UserID, CategoryID, getdate()
FROM dbo.Users cross join dbo.Categories
WHERE UserName = 'Apple'
AND Description = 'Mobile phones' ;
Also, I don't think including the numbers when looking at what categories which users act in is useful; so I would change Luis's final query example to
SELECT u.Username,c.Description
FROM dbo.Users u
JOIN dbo.UserCategories uc ON u.UserID = uc.UserID
JOIN dbo.Categories c ON uc.CategoryID = c.CategoryID ;
Tom
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply