How to link other tables (entities) to parent and child categories?

Question

I am not a developer by any means, but I have a fairly good understanding of how relational databases work and the various types. I am trying to map a database design diagram (using Visio) and need to understand how I would represent the correct relationships in the correct way for a table which needs to reference Parent/Child categories.

As an example: A member/profile table linking to a product parent category table (reference table containing 3 different product types) which also has 2 layers of sub categories for each parent as a tree like structure that drills down into more specific products. I understand the Parent/Child relationships but would I need to link the member/profile table to the Parent Category table (which inherently links to the child categories), or would I need to link the member/profile table to all three tables individually (parent and two child tables).

What I am trying to represent within the diagram, to ensure this is correct is that this database will be used to drive a front end website and when a user selects a Parent Category type from a drop down list, depending on the category chosen, it would then present the appropriate drop down lists for the associated child categories, allowing the specific Product Types and Child categories products to be associated with the member.

I hope this makes sense and I have explained this in a way that someone will understand. I just need to know how to represent this correctly within Visio.

Answer 1

I am going to suggest an alternate solution.

Rather than trying to mock-up a database design in Visio, why don't you create the database design in the relational database? Once that is complete you can use software to create the database diagram from your actual database. Most relational databases have database diagrams built in, and if the built in diagrams don't fit your needs there are a variety of third-party alternatives, many of them free, or with trial periods.

The advantages to this approach is that doing the actual design will help you think through your choices, and even if it doesn't end up being the final design, you can use the work as a starting point.

Answer 2

I think you might be making your life more complicated by conflating the requirement of your database with the process of meeting that requirement.

Databases are a static representation of your business data. If I understand your question correctly, you have a two step process of reaching the desired end state. In the first step, the user picks a top level category, then in the second step they pick a lower level category from options based on the parent/child relationships between the categories.

You only need one relationship in your database: your objects have a foreign key pointing at the lower level category. This is what you persist in your database.

Unless your user interface lets you save an object with only a top level category (and not a lower level category) then there is no relationship between the object and the top level category, except the indirect relationship via the lower level category.