CODEWITHSUNDEEP
sql.netazure
Green_qaue
Green_qaue

Reputation: 3661

SQL Azure database design issue

I am building a database to store Categories, and every Category has multiple sub-categories.

I am trying to find the best way to design this but Cant really think of anything except creating one table for each category, this feels a bit messy seeing how there will be at least 50 Categories. Is there a way I can create a "Category"-table and somehow have a column called "SubCategories" that lists multiple strings? If so, how would I split these subcategories so I can retrieve them without a problem from my application?

Upvotes: 0

Views: 47

Answers (1)

Sam Axe
Sam Axe

Reputation: 33738

Your comment is the solution most people would go with.

Create a table Categories, and a second table SubCategories.

Categories would have a unique primary key CategoryID (think Identity).

SubCategories would have a primary key SubCategoryID and a Foreign Key CategoryID

Now lets say you have a category in your code and you want to get all of its SubCategories:

DECLARE @CategoryID BIGINT = 23
SELECT s.SubCategoryID, s.CategoryID, s.Name 
FROM SubCategories s 
WHERE s.CategoryID = @CategoryID

Of course if you are using a database access technology like Entity Framework then you most likely won't be writing this kind of SQL as EF would take care of that for you.

Upvotes: 1

Related Questions