Conceptual help 1

[jisoo23]

Hello all!

I'm having a bit of a problem conceptualizing a data model here so I'm hoping someone can help me. Basically here's the rundown...

Customers can belong to 1 or more categories (some categories are sub-categories of others). Categories are arranged this way: there are several general categories, each of those has several sub-categories, and those sub-categories each have several of their own categories. What would be the best way to form the relationships between customers and their categories (sub or otherwise)?

What I initially set up so far is this:

customer_table
-customer_id (PK)
-customer_name
-customer_info

category_primary_table
-category_id (PK)
-category_name

category_secondary_table (sub-categories of primarys)
-sub_category_id (PK)
-category_id (FK)
-sub_category_name

category_third_table (sub-categories of secondarys)
-third_category_id (PK)
-sub_category_id {FK)
-third_category_name

I originally thought I could make an associative table that would link the customer_ids to category_ids but I run into the problem of "what if the customer belongs to a sub-category 1 or 2 levels down?". Likewise I don't think it's good practice to have 3 extra columns in the customer table with category, sub-category, etc. loaded into them (is it?). I'm not quite sure how to relate this. Does anyone have any ideas?

Thanks,
jisoo23

 

[alvechurchdata]

Does anyone have any ideas?

Sounds like many-to-many relationships between customer and each of the categories.

category_primary_link
-customer_id (FK)
-category_id (FK)

category_secondary_link
-customer_id (FK)
-sub_category_id (FK)

category_tertiary_link
-customer_id (FK)
-third_category_id (FK)

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[mrdenny]

The problem with having a table for each layer of catagories is that what if you want to add a fourth layer to the mix, or a fifth. Now you have to add tables, and modify code to allow for this. The last time I had to make a system that functioned like this, it was something like this.

Customers
--Customer_ID
--Customer_LastName
--Customer_FirstName
...Other Customer info here.

Catagories
--Catagory_ID int
--Catagory_Desc varchar(255)
--Parent_Catagory_ID int
...Other Cat info here.

Customers_Catagories
--Customer_ID
--Catagory_ID
...Both these columns make up the primary key.

Now you simply deal with finding the top level catagory in code. Something like this should do the trick.

create procedure usp_FindTopCatagory
   @Catagory_ID int
as
create table @TempCatID table
(PK int IDENTITY(1,1) NOT NULL,
Catagory_ID int,
Parent_Catagory_ID int)

insert into @TempCatID
(Catagory_ID, Parent_Catagory_ID)
select Catagory_ID, Parent_Catagory_ID)
from Catagories
where Catagory_ID = @Catagory_ID

while @@ROWCOUNT <> 0
BEGIN
  insert into @TempCatID
  (Catagory_ID, Parent_Catagory_ID)
  select Catagory_ID, Parent_Catagory_ID)
  from Catagories
  where Catagory_ID = @Catagory_ID
  and Catagory_ID not in (select Catagory_ID from @TempCatID)
END

select t.Catagory_ID, Catagories.Desc
from @TempCatID t
join Catagories on t.Catagory_ID, Catagories.Catagory_ID
order by t.PK DESC
go

The above procedure will return the catagory tree for the requested Sub Catagory up to the top level of the tree no matter how many forks thier are.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[jisoo23]

Thanks mrdenny! That's definitely a much more elegant solution than what I had cooked up. I have a question though, what is the overhead of this stored procedure? The creation of @TempCatID seems like it may take up a lot of resources if the source tables became rather large (perhaps 500 rows) wouldn't it? I only ask because this is a something that may be utilized many, many times over the course of a day.

Thanks for the help!
Jisoo23

 

[mrdenny]

With 500 records in the source table you shouldn't see any problems. If you get up to the hundreds of thousands of records you may start to see some performance issues.

Since the @TempCatID variable is a table variable and not a temp table it's created in memory, not in the tempdb database, so everything should go very fast. If you have hundreds or thousands of users running it at the same time, then memory may become an issue and you may want to convert it to a temp table. But as a table variable it will run faster. Also keep in mind, that no matter how big your source table is, this table variable will only hold how ever many records there are from the Child record, to the top of the chain. Probably a max of 8-10 records, at 8 bytes per record for a total of 64-80 bytes for each copy of the procedure while it's running. Not exactly a lot of data to hold in memory.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[jisoo23]

mrdenny,

Thanks for the info, this is actually very educational for me to learn. Question: when you say "top of the chain", do you mean the most general category (source of the tree) or one of the lowest points (an end node or tip of a branch)?

Appreciate to the help!
Jisoo23

 

[mrdenny]

Top of the chain" means the most general category.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)