Database Design of Categories

I am creating a website that has categories such as:

Sports -> Soccer -> Soccer Shoes

I can not think of an effectively way to implement this into a database. Please help.

Here is a website that has a bunch of sample database designs. If you don't find exactly what you want, the process of looking thru the other designs should help you.

http://www.databaseanswers.org/data_models/

A good data modeling book is by David Hay, Data Modeling Patterns: Conventions of Thought. He gives sample high-level data designs and talks thru different modeling alternatives so you fully understand the implications of subtle modeling differences.

|||

If you post what you've tried and why you didn't like it, I'll be happy to give you ideas on how to improve it.

|||

Thanks, that is a really cool site! I haven't looked at all the database designs because there are a lot of them but one that I found is #39 Books and Libraries. They have a "categories" table but all it stores is the CategoryID and the CategoryName. With just that information how to do you know where the category is in the hierarchy of categories? For example, you have Literature -> Books -> Fiction, say Fiction has CategoryID 20, how do you know where Fiction is in the hierarchy just by knowing the CategoryID?

I came across aneBay developer site that explains how they store their categories. What they do is they store the CategoryID but also the ID of it's parent. This way you know where it exists in the hierarchy. This is what the table would look like:

Category
CategoryID
ParentID
CategoryName
IsLeafCategory

What does everyone think of this solution? Does anyone have any alternative solutions?

|||

A better column name for ParentID would be ParentCategoryID. It's always better to be very clear.

This is a more powerful model than Literature->Books->Fiction because it can handle a "infinite" number of hierarchical levels.

It is also a more complicated one to use than a model in which you force the same number of levels in the hierarchy by using a different table for each level in the hierarchy.

IsLeafCategory is a redundant piece of information in that it can be deduced when required. I would not make use of it unless you have a specific need for it (because then you have to write code to make sure it is always correct).

Database design for College project

Hi,

I am very new to this form and I need urgent help and I hope someone can provide forme some light to my problem.

The college has a soccer team. And the application I am going to develope needs to use MS SQL Server 2000 Developer or Student edition.

I only mange to create these below table and I am not sure if this is the correct way to doing it.

The tables name:

tbl_TEAM: ID, StudentNumber, Name, Surname, Active
tbl_LEAGUE: ID, LeagueName, Active
tbl_WEEKS: ID, WeeksName, Active
tbl_FIXTURE: GameID, HomeTeam, VisitorTeam, Active
tbl_REFFERE: ReferreID, ReffereName, ReffereSurname, Active
tbl_OBSERVER: ObserverID, ObserverName, ObserverSurname, Active

The apliccation will create a fixture via user and then must assign reffere to each game. After the game the observer sends in the game report and user will use the report and assign the points for reffere.

Then Reffere sends the their reports and the application will assign the Goal, yellow card and Red card points to team member.

If any team member recevice 4 yellow card in total and not in one game then application will producea report to say this member team reach the point and have to sit one week and makes his yellow card points to zero. But in the end I have to know how much each team member had yellow card or red car in tottal.

If team member has goal he get 1 point and must show where is the goal. Because sometime they can make mistake and they have goal to their own teams net.

I am not sure if I can explaind correctly so I need help to develope the application and I am very new to soccer game as well as I don't know how to design the database.

Can anyone help me out to over come my SQL problem so I can finish my project before this christmas.

Thank you in advance.

Kind Regards,
NiyaziYou have not explained what your SQL problem is.

Your table design looks ok. But what is a "Fixture"?

If you want some help with SQL, post the code you have come up with so far and explain the errors or odd behavior you are getting. Do NOT expect us to do your homework for you.|||When creating tables, think about (and list out) all the objects you are trying to model (referees, games, players, etc.). These will likely end up as your tables.